﻿namespace WMS6.SQLServerDAL
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using WMS6.Common;
    using WMS6.DataEntity;
    using WMS6.DBUtility;
    using WMS6.IDAL;
    using WMS6.QueryEntity;

    public class ReceiptDetailDA : IReceiptDetailDA
    {
        private string ADJUSTED_QTY = "@ADJUSTED_QTY";
        private string ALT_SKU_ID = "@ALT_SKU_ID";
        private string CASE_CNT = "@CASE_CNT";
        private string CASE_ID = "@CASE_ID";
        private string CONDITION_CODE = "@CONDITION_CODE";
        private string CREATED_BY = "@CREATED_BY";
        private string CROSS_DOCK_ID = "@CROSS_DOCK_ID";
        private string CUBE = "@CUBE";
        private string DISPOSITION_CODE = "@DISPOSITION_CODE";
        private string DISPOSITION_TYPE = "@DISPOSITION_TYPE";
        private string EFFECTIVE_DATE = "@EFFECTIVE_DATE";
        private string EXPECTED_QTY = "@EXPECTED_QTY";
        private string EXPIRY_DATE = "@EXPIRY_DATE";
        private string EXTENDED_PRICE = "@EXTENDED_PRICE";
        private string EXTERN_LINE_ID = "@EXTERN_LINE_ID";
        private string EXTERN_RECEIPT_ID = "@EXTERN_RECEIPT_ID";
        private string EXTERNAL_LOT = "@EXTERNAL_LOT";
        private string GROSS_WGT = "@GROSS_WGT";
        private string INNER_PACK = "@INNER_PACK";
        private string LOT_ATTR01 = "@LOT_ATTR01";
        private string LOT_ATTR02 = "@LOT_ATTR02";
        private string LOT_ATTR03 = "@LOT_ATTR03";
        private string LOT_ATTR04 = "@LOT_ATTR04";
        private string LOT_ATTR05 = "@LOT_ATTR05";
        private string LOT_ATTR06 = "@LOT_ATTR06";
        private string LOT_ATTR07 = "@LOT_ATTR07";
        private string LOT_ATTR08 = "@LOT_ATTR08";
        private string LOT_ATTR09 = "@LOT_ATTR09";
        private string LOT_ATTR10 = "@LOT_ATTR10";
        private string LPN_ID = "@LPN_ID";
        private string MATCH_LOTTABLE = "@MATCH_LOTTABLE";
        private string NET_WGT = "@NET_WGT";
        private string OTHER_UNIT1 = "@OTHER_UNIT1";
        private string OTHER_UNIT2 = "@OTHER_UNIT2";
        private string OWNER_ID = "@OWNER_ID";
        private string PACK_ID = "@PACK_ID";
        private string PALLET = "@PALLET";
        private string PALLET_ID = "@PALLET_ID";
        private string PO_ID = "@PO_ID";
        private string PO_LINE_ID = "@PO_LINE_ID";
        private string PRODUCE_DATE = "@PRODUCE_DATE";
        private string QC_AUTO_ADJUST = "@QC_AUTO_ADJUST";
        private string QC_INSPECTED_QTY = "@QC_INSPECTED_QTY";
        private string QC_REJ_REASON = "@QC_REJ_REASON";
        private string QC_REJECTED_QTY = "@QC_REJECTED_QTY";
        private string QC_REQUIRED = "@QC_REQUIRED";
        private string QC_STATUS = "@QC_STATUS";
        private string QC_USER = "@QC_USER";
        private string REASON_CODE = "@REASON_CODE";
        private string RECEIPT_DATE = "@RECEIPT_DATE";
        private string RECEIPT_ID = "@RECEIPT_ID";
        private string RECEIPT_LINE_ID = "@RECEIPT_LINE_ID";
        private string RECEIVED_DATE = "@RECEIVED_DATE";
        private string RECEIVED_QTY = "@RECEIVED_QTY";
        private string REJECTED_QTY = "@REJECTED_QTY";
        private string REMARK = "@REMARK";
        private string RETURN_CONDITION = "@RETURN_CONDITION";
        private string RETURN_REASON = "@RETURN_REASON";
        private string RETURN_TYPE = "@RETURN_TYPE";
        private string RMA = "@RMA";
        private string SKU_ID = "@SKU_ID";
        private string SQL_CHECK_LOC_BY_IS_MULIT_LOT = " SELECT IS_MULTI_LOT FROM @_@LOCATION WHERE LOC = @LOC AND IS_ACTIVE = 'Y' ";
        private string SQL_CHECK_LOC_BY_IS_MULIT_SKU = " SELECT IS_MULTI_SKU FROM @_@LOCATION WHERE LOC = @LOC AND IS_ACTIVE = 'Y' ";
        private string SQL_CHECK_LOC_BY_LOC_QTY = " SELECT SUM(ISNULL(EXPECTED_QTY,0)) + SUM(ISNULL(QTY,0)) AS QTY FROM @_@INV_SKU_LOC WHERE LOC = @LOC ";
        private string SQL_CHECK_LOC_DIM_RESTRICTION_BY_CUBE = " SELECT CAST(L.CUBIC_CAPACITY - ISNULL(SUM((ISL.QTY+ISL.EXPECTED_QTY) * S.CUBE),0) AS DECIMAL) AS AVAILABLE_CUBE  FROM @_@LOCATION L  LEFT JOIN @_@INV_SKU_LOC ISL ON ISL.LOC = L.LOC LEFT JOIN @_@SKU S ON S.OWNER_ID = ISL.OWNER_ID AND S.SKU_ID = ISL.SKU_ID LEFT JOIN @_@PACK P ON P.PACK_ID = S.PACK_ID  WHERE L.LOC = @LOC GROUP BY L.CUBIC_CAPACITY ";
        private string SQL_CHECK_LOC_DIM_RESTRICTION_BY_PALLET_CUBE = " SELECT CAST(L.CUBIC_CAPACITY - ISNULL(SUM((ISL.QTY+ISL.EXPECTED_QTY) * S.CUBE),0) - ISNULL((L.STACK_LIMIT * (PALLET_WOOD_LENGTH*PALLET_WOOD_WIDTH*PALLET_WOOD_HEIGHT)),0) AS DECIMAL) AS AVAILABLE_CUBE  FROM @_@LOCATION L  LEFT JOIN @_@INV_SKU_LOC ISL ON ISL.LOC = L.LOC LEFT JOIN @_@SKU S ON S.OWNER_ID = ISL.OWNER_ID AND S.SKU_ID = ISL.SKU_ID LEFT JOIN @_@PACK P ON P.PACK_ID = S.PACK_ID  WHERE L.LOC = @LOC GROUP BY L.CUBIC_CAPACITY,L.STACK_LIMIT,P.PALLET_WOOD_LENGTH,P.PALLET_WOOD_WIDTH,P.PALLET_WOOD_HEIGHT ";
        private string SQL_CHECK_LOC_DIM_RESTRICTION_BY_PALLET_QTY = " SELECT CAST(L.CUBIC_CAPACITY - ISNULL(SUM((ISL.QTY+ISL.EXPECTED_QTY) * S.CUBE) ,0) - ISNULL((L.STACK_LIMIT * (PALLET_WOOD_LENGTH*PALLET_WOOD_WIDTH*PALLET_WOOD_HEIGHT)) ,0) AS DECIMAL) AS AVAILABLE_CUBE  FROM @_@LOCATION L  LEFT JOIN @_@INV_SKU_LOC ISL ON ISL.LOC = L.LOC LEFT JOIN @_@SKU S ON S.OWNER_ID = ISL.OWNER_ID AND S.SKU_ID = ISL.SKU_ID LEFT JOIN @_@PACK P ON P.PACK_ID = S.PACK_ID  WHERE L.LOC = @LOC GROUP BY L.CUBIC_CAPACITY,L.STACK_LIMIT,P.PALLET_WOOD_LENGTH,P.PALLET_WOOD_WIDTH,P.PALLET_WOOD_HEIGHT ";
        private string SQL_CHECK_LOC_DIM_RESTRICTION_BY_QTY = " SELECT CAST(L.CUBIC_CAPACITY - ISNULL(SUM((ISL.QTY+ISL.EXPECTED_QTY) * S.CUBE),0) AS DECIMAL) AS AVAILABLE_CUBE  FROM @_@LOCATION L  LEFT JOIN @_@INV_SKU_LOC ISL ON ISL.LOC = L.LOC LEFT JOIN @_@SKU S ON S.OWNER_ID = ISL.OWNER_ID AND S.SKU_ID = ISL.SKU_ID LEFT JOIN @_@PACK P ON P.PACK_ID = S.PACK_ID  WHERE L.LOC = @LOC GROUP BY L.CUBIC_CAPACITY ";
        private string SQL_CHECK_LOC_DIM_RESTRICTION_BY_WGT = " SELECT CAST(L.WEIGHT_CAPACITY - ISNULL(SUM((ISL.QTY+ISL.EXPECTED_QTY) * S.GROSS_WGT),0) AS DECIMAL) AS AVAILABLE_WGT  FROM @_@LOCATION L  LEFT JOIN @_@INV_SKU_LOC ISL ON ISL.LOC = L.LOC LEFT JOIN @_@SKU S ON S.OWNER_ID = ISL.OWNER_ID AND S.SKU_ID = ISL.SKU_ID LEFT JOIN @_@PACK P ON P.PACK_ID = S.PACK_ID  WHERE L.LOC = @LOC GROUP BY L.WEIGHT_CAPACITY ";
        private string SQL_CHECK_RECEIPT_DETAIL_ID_UNIQUE = " SELECT COUNT(1) FROM @_@RECEIPT_DETAIL WHERE RECEIPT_ID = @RECEIPT_ID AND RECEIPT_LINE_ID = @RECEIPT_LINE_ID ";
        private string SQL_CHECK_RECEIPT_IS_PALLETIZED = " SELECT IS_PALLETIZED FROM @_@RECEIPT WHERE RECEIPT_ID = @RECEIPT_ID ";
        private string SQL_DELETE_PUTAWAY_TASK_DETAIL = " DELETE FROM @_@TASK_DETAIL WHERE FROM_TRANS_ID = @FROM_TRANS_ID AND STATUS = 'Pending' ";
        private string SQL_DELETE_RECEIPT_DETAIL = " DELETE FROM @_@RECEIPT_DETAIL WHERE  ";
        private string SQL_GET_PENDIND_PA_TASK_BY_TRANS_ID = " SELECT TD.FROM_TRANS_ID,TD.DOC_ID,TD.DOC_LINE_ID,TD.SKU_ID,TD.OWNER_ID,IT.UOM_ID,IT.PACK_ID,TD.FROM_LOC,TD.TO_LOC,TD.TO_LPN_ID,IT.LOT,TD.QTY,IT.HOLD_CODE,TD.UPDATED_BY,TD.WH_ID,TD.FROM_LPN_ID,TD.CASE_ID  FROM @_@TASK_DETAIL TD LEFT JOIN @_@INV_TRANS IT ON TD.FROM_TRANS_ID = IT.TRANS_ID WHERE TD.TASK_TYPE = 'PA' AND TD.STATUS = 'PENDING' AND TD.FROM_TRANS_ID = @TRANS_ID ";
        private string SQL_GET_RECEIVED_LPN_COUNT = "SELECT count(*) RECEIVED_LPN FROM @_@INV_TRANS WHERE RECEIPT_ID = @RECEIPT_ID AND SOURCE_TRANS_TYPE = 'RECEIPTDETAILADD' AND STATUS = 'OK'; SELECT count(*) ORIGINAL_LPN FROM @_@LPN WHERE DOC_ID = @RECEIPT_ID AND DOC_TYPE = 'IN' AND LPN_ID LIKE '%0000'";
        private string SQL_GET_RECEIVED_QTY_BY_RECEIPT_DETAIL_ID = "SELECT SUM(QTY) AS RECEIVED_QTY FROM @_@INV_TRANS WHERE TRANS_TYPE = 'IN' AND SOURCE_TRANS_TYPE = 'RECEIPTDETAILADD' AND STATUS = 'OK' AND RECEIPT_ID = @RECEIPT_ID AND RECEIPT_LINE_ID = @RECEIPT_LINE_ID ";
        private string SQL_GET_TOTAL_EXPECTED_QTY_AND_RECEIVED_QTY = "SELECT SUM(EXPECTED_QTY) AS TOTAL_EXPECTED_QTY,SUM(RECEIVED_QTY) AS TOTAL_RECEIVED_QTY FROM @_@RECEIPT_DETAIL WHERE RECEIPT_ID = @RECEIPT_ID";
        private string SQL_INSERT_RECEIPT_DETAIL = " INSERT INTO @_@RECEIPT_DETAIL ( WH_ID, RECEIPT_ID, RECEIPT_LINE_ID, EXTERN_RECEIPT_ID, EXTERN_LINE_ID, OWNER_ID, PO_ID, PO_LINE_ID, TARIFF_ID, SKU_ID, ALT_SKU_ID, LPN_ID, CASE_ID, STATUS, RECEIVED_DATE, EXPECTED_QTY, ADJUSTED_QTY, RECEIVED_QTY, REJECTED_QTY, UOM_ID, PACK_ID, TO_LOC, TO_LOT, TO_LPN_ID, CONDITION_CODE, LOT_ATTR01, LOT_ATTR02, LOT_ATTR03, LOT_ATTR04, LOT_ATTR05, LOT_ATTR06, LOT_ATTR07, LOT_ATTR08, LOT_ATTR09, LOT_ATTR10, PRODUCE_DATE, EXPIRY_DATE, RECEIPT_DATE, EXTERNAL_LOT, CASE_CNT, INNER_PACK, PALLET, CUBE, GROSS_WGT, NET_WGT, OTHER_UNIT1, OTHER_UNIT2, UNIT_PRICE, EXTENDED_PRICE, EFFECTIVE_DATE, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REASON_CODE, PALLET_ID, TYPE, RETURN_TYPE, RETURN_REASON, DISPOSITION_TYPE, DISPOSITION_CODE, RETURN_CONDITION, QC_REQUIRED, QC_INSPECTED_QTY, QC_REJECTED_QTY, QC_REJ_REASON, QC_STATUS, QC_USER, QC_AUTO_ADJUST, RMA, VENDOR_ID, VENDOR_NAME, MATCH_LOTTABLE, CROSS_DOCK_ID, REMARK, CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) VALUES ( @WH_ID, @RECEIPT_ID, @RECEIPT_LINE_ID, @EXTERN_RECEIPT_ID, @EXTERN_LINE_ID, @OWNER_ID, @PO_ID, @PO_LINE_ID, @TARIFF_ID, @SKU_ID, @ALT_SKU_ID, @LPN_ID, @CASE_ID, @STATUS, @RECEIVED_DATE, @EXPECTED_QTY, @ADJUSTED_QTY, @RECEIVED_QTY, @REJECTED_QTY, @UOM_ID, @PACK_ID, @TO_LOC, @TO_LOT, @TO_LPN_ID, @CONDITION_CODE, @LOT_ATTR01, @LOT_ATTR02, @LOT_ATTR03, @LOT_ATTR04, @LOT_ATTR05, @LOT_ATTR06, @LOT_ATTR07, @LOT_ATTR08, @LOT_ATTR09, @LOT_ATTR10, @PRODUCE_DATE, @EXPIRY_DATE, @RECEIPT_DATE, @EXTERNAL_LOT, @CASE_CNT, @INNER_PACK, @PALLET, @CUBE, @GROSS_WGT, @NET_WGT, @OTHER_UNIT1, @OTHER_UNIT2, @UNIT_PRICE, @EXTENDED_PRICE, @EFFECTIVE_DATE, @UDF1, @UDF2, @UDF3, @UDF4, @UDF5, @UDF6, @UDF7, @UDF8, @UDF9, @UDF10, @REASON_CODE, @PALLET_ID, @TYPE, @RETURN_TYPE, @RETURN_REASON, @DISPOSITION_TYPE, @DISPOSITION_CODE, @RETURN_CONDITION, @QC_REQUIRED, @QC_INSPECTED_QTY, @QC_REJECTED_QTY, @QC_REJ_REASON, @QC_STATUS, @QC_USER, @QC_AUTO_ADJUST, @RMA, @VENDOR_ID, @VENDOR_NAME, @MATCH_LOTTABLE, @CROSS_DOCK_ID, @REMARK, @CREATED_BY, GETDATE(), @UPDATED_BY, GETDATE() )  ";
        private string SQL_IS_COPY = " INSERT INTO @_@RECEIPT_DETAIL(WH_ID,RECEIPT_ID,RECEIPT_LINE_ID,EXTERN_RECEIPT_ID, EXTERN_LINE_ID, OWNER_ID, PO_ID, PO_LINE_ID, TARIFF_ID, SKU_ID, ALT_SKU_ID, LPN_ID, CASE_ID, STATUS, RECEIVED_DATE, EXPECTED_QTY, ADJUSTED_QTY, RECEIVED_QTY, REJECTED_QTY,UOM_ID, PACK_ID, TO_LOC, TO_LOT, TO_LPN_ID, CONDITION_CODE, LOT_ATTR01, LOT_ATTR02, LOT_ATTR03, LOT_ATTR04, LOT_ATTR05, LOT_ATTR06, LOT_ATTR07, LOT_ATTR08, LOT_ATTR09, LOT_ATTR10, PRODUCE_DATE, EXPIRY_DATE, RECEIPT_DATE, EXTERNAL_LOT, CASE_CNT, INNER_PACK, PALLET, CUBE, GROSS_WGT, NET_WGT, OTHER_UNIT1, OTHER_UNIT2, UNIT_PRICE, EXTENDED_PRICE, EFFECTIVE_DATE, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REASON_CODE, PALLET_ID, TYPE, RETURN_TYPE, RETURN_REASON, DISPOSITION_TYPE, DISPOSITION_CODE, RETURN_CONDITION, QC_REQUIRED, QC_INSPECTED_QTY, QC_REJECTED_QTY, QC_REJ_REASON, QC_STATUS, QC_USER, QC_AUTO_ADJUST, RMA, VENDOR_ID, VENDOR_NAME, MATCH_LOTTABLE, CROSS_DOCK_ID, REMARK,  CREATED_BY, CREATED_DATE, UPDATED_BY, UPDATED_DATE) SELECT WH_ID,'@RECEIPT_ID', RECEIPT_LINE_ID, EXTERN_RECEIPT_ID, EXTERN_LINE_ID, OWNER_ID, PO_ID, PO_LINE_ID, TARIFF_ID, SKU_ID, ALT_SKU_ID, LPN_ID, CASE_ID, 'New', RECEIVED_DATE, EXPECTED_QTY, 0, 0, 0,UOM_ID, PACK_ID, TO_LOC, TO_LOT, TO_LPN_ID, CONDITION_CODE, LOT_ATTR01, LOT_ATTR02, LOT_ATTR03, LOT_ATTR04, LOT_ATTR05, LOT_ATTR06, LOT_ATTR07, LOT_ATTR08, LOT_ATTR09, '@RECEIPT_ID', PRODUCE_DATE, EXPIRY_DATE, RECEIPT_DATE, EXTERNAL_LOT, CASE_CNT, INNER_PACK, PALLET, CUBE, GROSS_WGT, NET_WGT, OTHER_UNIT1, OTHER_UNIT2, UNIT_PRICE, EXTENDED_PRICE, EFFECTIVE_DATE, UDF1, UDF2, UDF3, UDF4, UDF5, UDF6, UDF7, UDF8, UDF9, UDF10, REASON_CODE, PALLET_ID, TYPE, RETURN_TYPE, RETURN_REASON, DISPOSITION_TYPE, DISPOSITION_CODE, RETURN_CONDITION, QC_REQUIRED, QC_INSPECTED_QTY, QC_REJECTED_QTY, QC_REJ_REASON, QC_STATUS, QC_USER, QC_AUTO_ADJUST, RMA, VENDOR_ID, VENDOR_NAME, MATCH_LOTTABLE, CROSS_DOCK_ID, REMARK,  '@USER_ID', GETDATE(), '@USER_ID', GETDATE() FROM @_@RECEIPT_DETAIL WHERE RECEIPT_ID='@OLD_RECEIPT_ID'";
        private string SQL_SELECT_ALL_PUTAWAY_DETAIL = " SELECT TD.TASK_DETAIL_ID,TD.DOC_ID AS RECEIPT_ID,TD.DOC_LINE_ID AS RECEIPT_LINE_ID,TD.OWNER_ID,TD.SKU_ID,S.DESCR,TD.LOT,TD.TO_LPN_ID,TD.QTY,S.PACK_ID,TD.UOM_ID,TD.FROM_LPN_ID,TD.FROM_LOC,TD.TO_LOC,TD.STATUS,TD.FINAL_TO_LOC,T.[TEXT] AS STATUS_D FROM @_@TASK_DETAIL TD LEFT JOIN @_@SKU S ON TD.SKU_ID = S.SKU_ID AND TD.OWNER_ID = S.OWNER_ID LEFT JOIN sysadm.TRANSLATION T ON 'TaskStatus.'+TD.STATUS = T.ITEM_ID AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE WHERE TD.TASK_TYPE = 'PA' AND TD.DOC_ID = @RECEIPT_ID ";
        private string SQL_SELECT_ALL_RECEIPT_DETAIL = " SELECT  RD.ROWID, RD.WH_ID, RD.RECEIPT_ID, RD.RECEIPT_LINE_ID, RD.EXTERN_RECEIPT_ID, RD.EXTERN_LINE_ID, RD.OWNER_ID, RD.PO_ID, RD.PO_LINE_ID, RD.TARIFF_ID, RD.SKU_ID, SKU.DESCR AS SKU_DESCR, RD.ALT_SKU_ID, RD.LPN_ID, RD.CASE_ID, RD.STATUS,T.[TEXT] AS STATUS_D, RD.RECEIVED_DATE, RD.EXPECTED_QTY, RD.ADJUSTED_QTY, RD.RECEIVED_QTY, RD.REJECTED_QTY, RD.UOM_ID, RD.PACK_ID, RD.TO_LOC, RD.TO_LOT, RD.TO_LPN_ID, RD.CONDITION_CODE, RD.LOT_ATTR01, RD.LOT_ATTR02, RD.LOT_ATTR03, RD.LOT_ATTR04, RD.LOT_ATTR05, RD.LOT_ATTR06, RD.LOT_ATTR07, RD.LOT_ATTR08, RD.LOT_ATTR09, RD.LOT_ATTR10, RD.PRODUCE_DATE, RD.EXPIRY_DATE, RD.RECEIPT_DATE, RD.EXTERNAL_LOT, RD.CASE_CNT, RD.INNER_PACK, RD.PALLET, RD.CUBE, RD.GROSS_WGT, RD.NET_WGT, RD.OTHER_UNIT1, RD.OTHER_UNIT2, RD.UNIT_PRICE, RD.EXTENDED_PRICE, RD.EFFECTIVE_DATE, RD.UDF1, RD.UDF2, RD.UDF3, RD.UDF4, RD.UDF5, RD.UDF6, RD.UDF7, RD.UDF8, RD.UDF9, RD.UDF10, RD.REASON_CODE, RD.PALLET_ID, RD.TYPE, RD.RETURN_TYPE, RD.RETURN_REASON, RD.DISPOSITION_TYPE, RD.DISPOSITION_CODE, RD.RETURN_CONDITION, RD.QC_REQUIRED, RD.QC_INSPECTED_QTY, RD.QC_REJECTED_QTY, RD.QC_REJ_REASON, RD.QC_STATUS, RD.QC_USER, RD.QC_AUTO_ADJUST, RD.RMA, RD.VENDOR_ID, RD.VENDOR_NAME, RD.MATCH_LOTTABLE, RD.CROSS_DOCK_ID, RD.REMARK, RD.TS, RD.CREATED_BY, RD.CREATED_DATE, RD.UPDATED_BY, RD.UPDATED_DATE,R.RECEIPT_TYPE,R.RMA   FROM  @_@RECEIPT_DETAIL RD  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = RD.STATUS AND SCD.SYS_CODE_TYPE = 'ReceiptStatus' LEFT JOIN sysadm.TRANSLATION T ON 'ReceiptStatus.'+RD.STATUS = T.ITEM_ID  \t\t\tAND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE LEFT JOIN @_@SKU SKU ON RD.OWNER_ID = SKU.OWNER_ID AND RD.SKU_ID = SKU.SKU_ID  LEFT JOIN @_@RECEIPT R ON RD.RECEIPT_ID = R.RECEIPT_ID  INNER JOIN SYSADM.USER_OWNER UO ON RD.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ";
        private string SQL_SELECT_ALL_RECEIPT_DETAIL_LPN = " SELECT  RD.ROWID, RD.WH_ID, RD.RECEIPT_ID, RD.RECEIPT_LINE_ID, RD.EXTERN_RECEIPT_ID, RD.EXTERN_LINE_ID, RD.OWNER_ID, RD.PO_ID, RD.PO_LINE_ID, RD.TARIFF_ID, RD.SKU_ID, SKU.DESCR AS SKU_DESCR, RD.ALT_SKU_ID, RD.LPN_ID, RD.CASE_ID, RD.STATUS,T.[TEXT] AS STATUS_D, RD.RECEIVED_DATE, RD.EXPECTED_QTY, RD.ADJUSTED_QTY, RD.RECEIVED_QTY, RD.REJECTED_QTY, RD.UOM_ID, RD.PACK_ID, RD.TO_LOC, RD.TO_LOT, RD.TO_LPN_ID, RD.CONDITION_CODE, RD.LOT_ATTR01, RD.LOT_ATTR02, RD.LOT_ATTR03, RD.LOT_ATTR04, RD.LOT_ATTR05, RD.LOT_ATTR06, RD.LOT_ATTR07, RD.LOT_ATTR08, RD.LOT_ATTR09, RD.LOT_ATTR10, RD.PRODUCE_DATE, RD.EXPIRY_DATE, RD.RECEIPT_DATE, RD.EXTERNAL_LOT, RD.CASE_CNT, RD.INNER_PACK, RD.PALLET, RD.CUBE, RD.GROSS_WGT, RD.NET_WGT, RD.OTHER_UNIT1, RD.OTHER_UNIT2, RD.UNIT_PRICE, RD.EXTENDED_PRICE, RD.EFFECTIVE_DATE, RD.UDF1, RD.UDF2, RD.UDF3, RD.UDF4, RD.UDF5, RD.UDF6, RD.UDF7, RD.UDF8, RD.UDF9, RD.UDF10, RD.REASON_CODE, RD.PALLET_ID, RD.TYPE, RD.RETURN_TYPE, RD.RETURN_REASON, RD.DISPOSITION_TYPE, RD.DISPOSITION_CODE, RD.RETURN_CONDITION, RD.QC_REQUIRED, RD.QC_INSPECTED_QTY, RD.QC_REJECTED_QTY, RD.QC_REJ_REASON, RD.QC_STATUS, RD.QC_USER, RD.QC_AUTO_ADJUST, RD.RMA, RD.VENDOR_ID, RD.VENDOR_NAME, RD.MATCH_LOTTABLE, RD.CROSS_DOCK_ID, RD.REMARK, RD.TS, RD.CREATED_BY, RD.CREATED_DATE, RD.UPDATED_BY, RD.UPDATED_DATE,R.RECEIPT_TYPE,R.RMA   FROM  @_@RECEIPT_DETAIL_LPN RD  LEFT JOIN @_@SYS_CODE_DETAIL SCD ON SCD.SYS_CODE = RD.STATUS AND SCD.SYS_CODE_TYPE = 'ReceiptStatus' LEFT JOIN sysadm.TRANSLATION T ON 'ReceiptStatus.'+RD.STATUS = T.ITEM_ID  \t\t\tAND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE LEFT JOIN @_@SKU SKU ON RD.OWNER_ID = SKU.OWNER_ID AND RD.SKU_ID = SKU.SKU_ID  LEFT JOIN @_@RECEIPT R ON RD.RECEIPT_ID = R.RECEIPT_ID  INNER JOIN SYSADM.USER_OWNER UO ON RD.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID ";
        private string SQL_SELECT_ALL_RECEIPT_HISTORY = " SELECT IT.TRANS_ID,IT.RECEIPT_ID,IT.RECEIPT_LINE_ID,IT.SKU_ID,IT.OWNER_ID,RD.VENDOR_ID,IT.PACK_ID,IT.UOM_ID, IT.TO_LPN_ID,IT.LOT,IT.TO_LOC,IT.QTY,LAC.DESCR AS HOLD_CODE,SUM(ISNULL(TD.QTY,0)) AS COMPLETED_QTY, IT.QTY-SUM(ISNULL(TD.QTY,0))-SUM(ISNULL(PD.QTY,0)) AS REMAIN_QTY, SUM(ISNULL(PD.QTY,0)) AS ALLOCATED_QTY FROM @_@INV_TRANS IT  LEFT JOIN @_@RECEIPT_DETAIL RD ON IT.RECEIPT_ID = RD.RECEIPT_ID AND IT.RECEIPT_LINE_ID = RD.RECEIPT_LINE_ID  LEFT JOIN @_@TASK_DETAIL TD ON IT.TRANS_ID = TD.FROM_TRANS_ID AND TD.STATUS = 'Completed' LEFT JOIN @_@LOT_ATTRIBUTE LA ON IT.LOT = LA.LOT AND LA.LOT_ATTR10 = @RECEIPT_ID LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON LA.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性' LEFT JOIN @_@PICK_DETAIL PD ON IT.LOT = PD.LOT AND IT.TO_LOC = PD.LOC AND IT.TO_LPN_ID = PD.LPN_ID AND PD.STATUS IN ('NEW','RELEASE','PICKED','SHIPPED')  @CONDITION@ GROUP BY IT.TRANS_ID,IT.RECEIPT_ID,IT.RECEIPT_LINE_ID,IT.SKU_ID,IT.OWNER_ID,RD.VENDOR_ID,IT.PACK_ID,IT.UOM_ID, IT.TO_LPN_ID,IT.LOT,IT.TO_LOC,IT.QTY,LAC.DESCR,TD.STATUS ";
        private string SQL_SELECT_ALL_RECEIPT_HISTORY_LOT = " SELECT IT.TRANS_ID,IT.RECEIPT_ID,IT.RECEIPT_LINE_ID,IT.SKU_ID,IT.OWNER_ID,RD.VENDOR_ID,IT.PACK_ID,IT.UOM_ID, IT.TO_LPN_ID,IT.LOT,IT.TO_LOC,IT.QTY,LAC.DESCR AS HOLD_CODE,SUM(ISNULL(TD.QTY,0)) AS COMPLETED_QTY, IT.QTY-SUM(ISNULL(TD.QTY,0))-SUM(ISNULL(PD.QTY,0)) AS REMAIN_QTY, SUM(ISNULL(PD.QTY,0)) AS ALLOCATED_QTY,LA.LOT_ATTR01,LA.LOT_ATTR02,LA.LOT_ATTR03,LA.LOT_ATTR04,LA.LOT_ATTR05,LA.LOT_ATTR06,LA.LOT_ATTR07,LA.LOT_ATTR08,LA.LOT_ATTR09,LA.LOT_ATTR10,LA.PRODUCE_DATE,LA.EXPIRY_DATE,LA.RECEIPT_DATE,LA.EXTERNAL_LOT,S.DESCR FROM @_@INV_TRANS IT  LEFT JOIN @_@RECEIPT_DETAIL RD ON IT.RECEIPT_ID = RD.RECEIPT_ID AND IT.RECEIPT_LINE_ID = RD.RECEIPT_LINE_ID  LEFT JOIN @_@SKU S ON IT.SKU_ID= S.SKU_ID AND IT.OWNER_ID = S.OWNER_ID LEFT JOIN @_@TASK_DETAIL TD ON IT.TRANS_ID = TD.FROM_TRANS_ID AND TD.STATUS = 'Completed' LEFT JOIN @_@LOT_ATTRIBUTE LA ON IT.LOT = LA.LOT AND LA.LOT_ATTR10 = @RECEIPT_ID LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON LA.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性' LEFT JOIN @_@PICK_DETAIL PD ON IT.LOT = PD.LOT AND IT.TO_LOC = PD.LOC AND IT.TO_LPN_ID = PD.LPN_ID AND PD.STATUS IN ('NEW','RELEASE','PICKED','SHIPPED')  @CONDITION@ GROUP BY IT.TRANS_ID,IT.RECEIPT_ID,IT.RECEIPT_LINE_ID,IT.SKU_ID,IT.OWNER_ID,RD.VENDOR_ID,IT.PACK_ID,IT.UOM_ID, IT.TO_LPN_ID,IT.LOT,IT.TO_LOC,IT.QTY,LAC.DESCR,TD.STATUS,LA.LOT_ATTR01,LA.LOT_ATTR02,LA.LOT_ATTR03,LA.LOT_ATTR04,LA.LOT_ATTR05,LA.LOT_ATTR06,LA.LOT_ATTR07,LA.LOT_ATTR08,LA.LOT_ATTR09,LA.LOT_ATTR10,LA.PRODUCE_DATE,LA.EXPIRY_DATE,LA.RECEIPT_DATE,LA.EXTERNAL_LOT,S.DESCR ";
        private string SQL_SELECT_LOC_BY_APPOINT_ZONE = " SELECT L.LOC FROM @_@LOCATION L LEFT JOIN @_@ZONE Z ON L.ZONE_ID = Z.ZONE_ID WHERE Z.ZONE_ID = @ZONE_ID AND L.STATUS <> 'HOLD' AND L.IS_ACTIVE = 'Y' AND Z.IS_ACTIVE = 'Y' ORDER BY LOC ";
        private string SQL_SELECT_LOC_BY_SKU_CASE_LOC = " SELECT LOC FROM @_@INV_SKU_LOC WHERE OWNER_ID = @OWNER_ID AND SKU_ID =@SKU_ID AND LOCATION_TYPE = 'CASE' ORDER BY LOC ";
        private string SQL_SELECT_LOC_BY_SKU_LOC = " SELECT PUTAWAY_LOC FROM @_@SKU WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID AND IS_ACTIVE = 'Y' ";
        private string SQL_SELECT_LOC_BY_SKU_PICK_LOC = " SELECT LOC FROM @_@INV_SKU_LOC WHERE OWNER_ID = @OWNER_ID AND SKU_ID =@SKU_ID AND LOCATION_TYPE = 'PIECE' ORDER BY LOC ";
        private string SQL_SELECT_LOC_BY_SKU_ZONE = " SELECT L.LOC FROM @_@LOCATION L LEFT JOIN @_@ZONE Z ON L.ZONE_ID = Z.ZONE_ID WHERE Z.ZONE_ID IN (SELECT ZONE_ID FROM @_@SKU WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID )  AND L.STATUS <> 'HOLD' AND L.IS_ACTIVE = 'Y' ORDER BY LOC ";
        private string SQL_SELECT_PUTAWAY_DETAIL_BY_LPN = " SELECT TD.TASK_DETAIL_ID,TD.DOC_ID AS RECEIPT_ID,TD.DOC_LINE_ID AS RECEIPT_LINE_ID,TD.OWNER_ID,TD.SKU_ID,S.DESCR,TD.LOT,TD.TO_LPN_ID,TD.QTY,S.PACK_ID,TD.UOM_ID,TD.FROM_LPN_ID,TD.FROM_LOC,TD.TO_LOC,TD.STATUS,TD.FINAL_TO_LOC,T.[TEXT] AS STATUS_D FROM @_@TASK_DETAIL TD LEFT JOIN @_@SKU S ON TD.SKU_ID = S.SKU_ID AND TD.OWNER_ID = S.OWNER_ID LEFT JOIN sysadm.TRANSLATION T ON 'TaskStatus.'+TD.STATUS = T.ITEM_ID AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE WHERE TD.TASK_TYPE = 'PA' AND TD.STATUS = 'PENDING' AND TD.TO_LPN_ID = @TO_LPN_ID ";
        private string SQL_UPDATE_RECEIPT_DETAIL = " UPDATE @_@RECEIPT_DETAIL SET WH_ID = @WH_ID, EXTERN_RECEIPT_ID = @EXTERN_RECEIPT_ID, EXTERN_LINE_ID = @EXTERN_LINE_ID, OWNER_ID = @OWNER_ID, PO_ID = @PO_ID, PO_LINE_ID = @PO_LINE_ID, TARIFF_ID = @TARIFF_ID, SKU_ID = @SKU_ID, ALT_SKU_ID = @ALT_SKU_ID, LPN_ID = @LPN_ID, CASE_ID = @CASE_ID, STATUS = @STATUS, RECEIVED_DATE = @RECEIVED_DATE, EXPECTED_QTY = @EXPECTED_QTY, ADJUSTED_QTY = @ADJUSTED_QTY, RECEIVED_QTY = @RECEIVED_QTY, REJECTED_QTY = @REJECTED_QTY, UOM_ID = @UOM_ID, PACK_ID = @PACK_ID, TO_LOC = @TO_LOC, TO_LOT = @TO_LOT, TO_LPN_ID = @TO_LPN_ID, CONDITION_CODE = @CONDITION_CODE, LOT_ATTR01 = @LOT_ATTR01, LOT_ATTR02 = @LOT_ATTR02, LOT_ATTR03 = @LOT_ATTR03, LOT_ATTR04 = @LOT_ATTR04, LOT_ATTR05 = @LOT_ATTR05, LOT_ATTR06 = @LOT_ATTR06, LOT_ATTR07 = @LOT_ATTR07, LOT_ATTR08 = @LOT_ATTR08, LOT_ATTR09 = @LOT_ATTR09, LOT_ATTR10 = @LOT_ATTR10, PRODUCE_DATE = @PRODUCE_DATE, EXPIRY_DATE = @EXPIRY_DATE, RECEIPT_DATE = @RECEIPT_DATE, EXTERNAL_LOT = @EXTERNAL_LOT, CASE_CNT = @CASE_CNT, INNER_PACK = @INNER_PACK, PALLET = @PALLET, CUBE = @CUBE, GROSS_WGT = @GROSS_WGT, NET_WGT = @NET_WGT, OTHER_UNIT1 = @OTHER_UNIT1, OTHER_UNIT2 = @OTHER_UNIT2, UNIT_PRICE = @UNIT_PRICE, EXTENDED_PRICE = @EXTENDED_PRICE, EFFECTIVE_DATE = @EFFECTIVE_DATE, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, REASON_CODE = @REASON_CODE, PALLET_ID = @PALLET_ID, TYPE = @TYPE, RETURN_TYPE = @RETURN_TYPE, RETURN_REASON = @RETURN_REASON, DISPOSITION_TYPE = @DISPOSITION_TYPE, DISPOSITION_CODE = @DISPOSITION_CODE, RETURN_CONDITION = @RETURN_CONDITION, QC_REQUIRED = @QC_REQUIRED, QC_INSPECTED_QTY = @QC_INSPECTED_QTY, QC_REJECTED_QTY = @QC_REJECTED_QTY, QC_REJ_REASON = @QC_REJ_REASON, QC_STATUS = @QC_STATUS, QC_USER = @QC_USER, QC_AUTO_ADJUST = @QC_AUTO_ADJUST, RMA = @RMA, VENDOR_ID = @VENDOR_ID, VENDOR_NAME = @VENDOR_NAME, MATCH_LOTTABLE = @MATCH_LOTTABLE, CROSS_DOCK_ID = @CROSS_DOCK_ID, REMARK = @REMARK, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE RECEIPT_ID = @RECEIPT_ID AND RECEIPT_LINE_ID = @RECEIPT_LINE_ID  ";
        private string SQL_UPDATE_RECEIPT_DETAIL_FOR_RECEIGING = " UPDATE @_@RECEIPT_DETAIL SET RECEIVED_QTY = @RECEIVED_QTY, RECEIVED_DATE = @RECEIVED_DATE, RECEIPT_DATE = @RECEIPT_DATE, STATUS = @STATUS,LPN_ID = @LPN_ID,TO_LOC = @TO_LOC,TO_LOT = @TO_LOT,TO_LPN_ID = @TO_LPN_ID,UPDATED_BY = @UPDATED_BY,UPDATED_DATE = GETDATE() WHERE RECEIPT_ID = @RECEIPT_ID AND RECEIPT_LINE_ID = @RECEIPT_LINE_ID ";
        private string SQL_UPDATE_RECEIPT_DETAIL_LPN = " UPDATE @_@RECEIPT_DETAIL_LPN SET WH_ID = @WH_ID, EXTERN_RECEIPT_ID = @EXTERN_RECEIPT_ID, EXTERN_LINE_ID = @EXTERN_LINE_ID, OWNER_ID = @OWNER_ID, PO_ID = @PO_ID, PO_LINE_ID = @PO_LINE_ID, TARIFF_ID = @TARIFF_ID, SKU_ID = @SKU_ID, ALT_SKU_ID = @ALT_SKU_ID, LPN_ID = @LPN_ID, CASE_ID = @CASE_ID, STATUS = @STATUS, RECEIVED_DATE = @RECEIVED_DATE, EXPECTED_QTY = @EXPECTED_QTY, ADJUSTED_QTY = @ADJUSTED_QTY, RECEIVED_QTY = @RECEIVED_QTY, REJECTED_QTY = @REJECTED_QTY, UOM_ID = @UOM_ID, PACK_ID = @PACK_ID, TO_LOC = @TO_LOC, TO_LOT = @TO_LOT, TO_LPN_ID = @TO_LPN_ID, CONDITION_CODE = @CONDITION_CODE, LOT_ATTR01 = @LOT_ATTR01, LOT_ATTR02 = @LOT_ATTR02, LOT_ATTR03 = @LOT_ATTR03, LOT_ATTR04 = @LOT_ATTR04, LOT_ATTR05 = @LOT_ATTR05, LOT_ATTR06 = @LOT_ATTR06, LOT_ATTR07 = @LOT_ATTR07, LOT_ATTR08 = @LOT_ATTR08, LOT_ATTR09 = @LOT_ATTR09, LOT_ATTR10 = @LOT_ATTR10, PRODUCE_DATE = @PRODUCE_DATE, EXPIRY_DATE = @EXPIRY_DATE, RECEIPT_DATE = @RECEIPT_DATE, EXTERNAL_LOT = @EXTERNAL_LOT, CASE_CNT = @CASE_CNT, INNER_PACK = @INNER_PACK, PALLET = @PALLET, CUBE = @CUBE, GROSS_WGT = @GROSS_WGT, NET_WGT = @NET_WGT, OTHER_UNIT1 = @OTHER_UNIT1, OTHER_UNIT2 = @OTHER_UNIT2, UNIT_PRICE = @UNIT_PRICE, EXTENDED_PRICE = @EXTENDED_PRICE, EFFECTIVE_DATE = @EFFECTIVE_DATE, UDF1 = @UDF1, UDF2 = @UDF2, UDF3 = @UDF3, UDF4 = @UDF4, UDF5 = @UDF5, UDF6 = @UDF6, UDF7 = @UDF7, UDF8 = @UDF8, UDF9 = @UDF9, UDF10 = @UDF10, REASON_CODE = @REASON_CODE, PALLET_ID = @PALLET_ID, TYPE = @TYPE, RETURN_TYPE = @RETURN_TYPE, RETURN_REASON = @RETURN_REASON, DISPOSITION_TYPE = @DISPOSITION_TYPE, DISPOSITION_CODE = @DISPOSITION_CODE, RETURN_CONDITION = @RETURN_CONDITION, QC_REQUIRED = @QC_REQUIRED, QC_INSPECTED_QTY = @QC_INSPECTED_QTY, QC_REJECTED_QTY = @QC_REJECTED_QTY, QC_REJ_REASON = @QC_REJ_REASON, QC_STATUS = @QC_STATUS, QC_USER = @QC_USER, QC_AUTO_ADJUST = @QC_AUTO_ADJUST, RMA = @RMA, VENDOR_ID = @VENDOR_ID, VENDOR_NAME = @VENDOR_NAME, MATCH_LOTTABLE = @MATCH_LOTTABLE, CROSS_DOCK_ID = @CROSS_DOCK_ID, REMARK = @REMARK, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE RECEIPT_ID = @RECEIPT_ID AND RECEIPT_LINE_ID = @RECEIPT_LINE_ID  ";
        private string SQL_UPDATE_RECEIPT_DETAIL_STAUTS = " UPDATE @_@RECEIPT_DETAIL SET STATUS = @STATUS, UPDATED_BY = @UPDATED_BY, UPDATED_DATE = GETDATE() WHERE RECEIPT_ID = @RECEIPT_ID ";
        private string SQL_UPDATE_TASK_DETAIL_STATUS = " UPDATE @_@TASK_DETAIL SET STATUS = @STATUS, FINAL_TO_LOC = @FINAL_TO_LOC, TO_TRANS_ID = @TO_TRANS_ID, UPDATED_BY = @UPDATED_BY WHERE TASK_DETAIL_ID = @TASK_DETAIL_ID ";
        private string STATUS = "@STATUS";
        private string TARIFF_ID = "@TARIFF_ID";
        private string TO_LOC = "@TO_LOC";
        private string TO_LOT = "@TO_LOT";
        private string TO_LPN_ID = "@TO_LPN_ID";
        private string TS = "@TS";
        private string TYPE = "@TYPE";
        private string UDF1 = "@UDF1";
        private string UDF10 = "@UDF10";
        private string UDF2 = "@UDF2";
        private string UDF3 = "@UDF3";
        private string UDF4 = "@UDF4";
        private string UDF5 = "@UDF5";
        private string UDF6 = "@UDF6";
        private string UDF7 = "@UDF7";
        private string UDF8 = "@UDF8";
        private string UDF9 = "@UDF9";
        private string UNIT_PRICE = "@UNIT_PRICE";
        private string UOM_ID = "@UOM_ID";
        private string UPDATED_BY = "@UPDATED_BY";
        private string VENDOR_ID = "@VENDOR_ID";
        private string VENDOR_NAME = "@VENDOR_NAME";
        private string WH_ID = "@WH_ID";

        public object CheckDimRestrictionByPAStratety(DataBase dataBase, DbTransaction tran, string suggestLoc, string dimRestriction, string whLoginID)
        {
            string sql = string.Empty;
            SqlParameter[] paras = null;
            string CS40001 = dimRestriction;
            if (CS40001 != null)
            {
                if (!(CS40001 == "20"))
                {
                    if (CS40001 == "30")
                    {
                        sql = this.SQL_CHECK_LOC_DIM_RESTRICTION_BY_WGT.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@LOC", suggestLoc) };
                    }
                    else if (CS40001 == "40")
                    {
                        sql = this.SQL_CHECK_LOC_DIM_RESTRICTION_BY_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@LOC", suggestLoc) };
                    }
                    else if (CS40001 == "50")
                    {
                        sql = this.SQL_CHECK_LOC_DIM_RESTRICTION_BY_PALLET_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@LOC", suggestLoc) };
                    }
                    else if (CS40001 == "60")
                    {
                        sql = this.SQL_CHECK_LOC_DIM_RESTRICTION_BY_PALLET_CUBE.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@LOC", suggestLoc) };
                    }
                }
                else
                {
                    sql = this.SQL_CHECK_LOC_DIM_RESTRICTION_BY_CUBE.Replace("@_@", whLoginID);
                    paras = new SqlParameter[] { new SqlParameter("@LOC", suggestLoc) };
                }
            }
            return dataBase.ExecuteScalar(tran, CommandType.Text, sql, paras);
        }

        public object CheckLocByLocFilter(DataBase dataBase, DbTransaction tran, string suggestLoc, string receiptID, string receiptLineID, string locSelection, string whLoginID)
        {
            string sql = " SELECT LOCATION.LOC FROM @_@RECEIPT RECEIPT, @_@LOCATION LOCATION,@_@RECEIPT_DETAIL RECEIPT_DETAIL WHERE LOCATION.LOC = @LOC AND RECEIPT.RECEIPT_ID = @RECEIPT_ID AND RECEIPT_DETAIL.RECEIPT_LINE_ID = @RECEIPT_LINE_ID AND RECEIPT.RECEIPT_ID = RECEIPT_DETAIL.RECEIPT_ID ";
            sql = (sql + " AND " + locSelection).Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOC", suggestLoc), new SqlParameter("@RECEIPT_ID", receiptID), new SqlParameter("@RECEIPT_LINE_ID", receiptLineID) };
            return dataBase.ExecuteScalar(tran, CommandType.Text, sql, paras);
        }

        public object CheckLocStatusLimitByPAStrategy(DataBase dataBase, DbTransaction tran, string suggestLoc, string locCheckType, string whLoginID)
        {
            string sql = string.Empty;
            SqlParameter[] paras = null;
            string CS40001 = locCheckType;
            if (CS40001 != null)
            {
                if (!(CS40001 == "20"))
                {
                    if (CS40001 == "30")
                    {
                        sql = this.SQL_CHECK_LOC_BY_LOC_QTY.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@LOC", suggestLoc) };
                    }
                    else if (CS40001 == "40")
                    {
                        sql = this.SQL_CHECK_LOC_BY_IS_MULIT_SKU.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@LOC", suggestLoc) };
                    }
                    else if (CS40001 == "50")
                    {
                        sql = this.SQL_CHECK_LOC_BY_IS_MULIT_LOT.Replace("@_@", whLoginID);
                        paras = new SqlParameter[] { new SqlParameter("@LOC", suggestLoc) };
                    }
                }
                else
                {
                    sql = this.SQL_CHECK_LOC_BY_LOC_QTY.Replace("@_@", whLoginID);
                    paras = new SqlParameter[] { new SqlParameter("@LOC", suggestLoc) };
                }
            }
            return dataBase.ExecuteScalar(tran, CommandType.Text, sql, paras);
        }

        public bool CheckReceiptDetailIDUnique(string receiptID, string receiptLineID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID), new SqlParameter("@RECEIPT_LINE_ID", receiptLineID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, this.SQL_CHECK_RECEIPT_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        public bool CheckReceiptDetailIDUnique(DataBase dataBase, DbTransaction tran, string receiptID, string receiptLineID, string whLoginID)
        {
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID), new SqlParameter("@RECEIPT_LINE_ID", receiptLineID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, this.SQL_CHECK_RECEIPT_DETAIL_ID_UNIQUE.Replace("@_@", whLoginID), paras).ToString() == "1");
        }

        private bool CheckReceiptIsPalletized(string receiptID, string whLoginID)
        {
            string sql = this.SQL_CHECK_RECEIPT_IS_PALLETIZED.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID) };
            return (DBHelper.ExecuteScalar(CommandType.Text, sql, paras).ToString() == "Y");
        }

        private bool CheckReceiptIsPalletized(DataBase dataBase, DbTransaction tran, string receiptID, string whLoginID)
        {
            string sql = this.SQL_CHECK_RECEIPT_IS_PALLETIZED.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID) };
            return (dataBase.ExecuteScalar(tran, CommandType.Text, sql, paras).ToString() == "Y");
        }

        public int DeletePutawayTaskDetail(DataBase dataBase, DbTransaction tran, string fromTransID, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = this.SQL_DELETE_PUTAWAY_TASK_DETAIL.Replace("@_@", whLoginID);
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@FROM_TRANS_ID", fromTransID) };
                result = dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteReceiptDetail(List<string> receiptDetailID, string whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                string sql = this.SQL_DELETE_RECEIPT_DETAIL.Replace("@_@", whLoginID) + " RECEIPT_ID = @RECEIPT_ID ";
                for (int j = 0; j < receiptDetailID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptDetailID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteReceiptDetail(List<string> receiptID, List<string> receiptLineID, string whLoginID)
        {
            int result = 0;
            DataBase dataBase = new DataBase();
            DbConnection conn = dataBase.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                string sql = this.SQL_DELETE_RECEIPT_DETAIL.Replace("@_@", whLoginID) + " RECEIPT_ID = @RECEIPT_ID  AND RECEIPT_LINE_ID = @RECEIPT_LINE_ID ";
                for (int j = 0; j < receiptID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID[j]), new SqlParameter("@RECEIPT_LINE_ID", receiptLineID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int DeleteReceiptDetail(DataBase dataBase, DbTransaction tran, List<string> receiptID, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = this.SQL_DELETE_RECEIPT_DETAIL.Replace("@_@", whLoginID) + " RECEIPT_ID = @RECEIPT_ID ";
                for (int j = 0; j < receiptID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int DeleteReceiptDetail(DataBase dataBase, DbTransaction tran, List<string> receiptID, List<string> receiptLineID, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = this.SQL_DELETE_RECEIPT_DETAIL.Replace("@_@", whLoginID) + " RECEIPT_ID = @RECEIPT_ID  AND RECEIPT_LINE_ID = @RECEIPT_LINE_ID ";
                for (int j = 0; j < receiptID.Count; j++)
                {
                    SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID[j]), new SqlParameter("@RECEIPT_LINE_ID", receiptLineID[j]) };
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public DataSet GetAllReceiptDetail(string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_RECEIPT_DETAIL.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetAllReceiptDetail(DataBase dataBase, DbTransaction tran, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_RECEIPT_DETAIL.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
        }

        public DataSet GetLotOnLoc(DataBase dataBase, DbTransaction tran, string loc, string whLoginID)
        {
            string sql = " SELECT DISTINCT ILLL.LOT,SUM(ILLL.QTY) AS QTY  FROM @_@INV_LOT_LOC_LPN ILLL LEFT JOIN  @_@TASK_DETAIL TD ON ILLL.LOT = TD.LOT  AND ILLL.OWNER_ID = TD.OWNER_ID AND ILLL.SKU_ID = TD.SKU_ID WHERE TD.STATUS = 'Pending' AND TASK_TYPE = 'PA'  AND TD.TO_LOC = @LOC  GROUP BY ILLL.LOT ";
            sql = sql.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOC", loc) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
        }

        public DataSet GetMandatoryLotAttributeInfo(string ownerID, string skuID, string whLoginID)
        {
            string sql = " SELECT PRODUCE_DATE_MANDATORY ,EXPIRY_DATE_MANDATORY ,LOT_ATTR01_MANDATORY ,LOT_ATTR02_MANDATORY ,LOT_ATTR03_MANDATORY ,LOT_ATTR04_MANDATORY ,LOT_ATTR05_MANDATORY ,LOT_ATTR06_MANDATORY ,LOT_ATTR07_MANDATORY ,LOT_ATTR08_MANDATORY ,LOT_ATTR09_MANDATORY ,LOT_ATTR10_MANDATORY FROM @_@LOT_TEMPLATE WHERE LOT_TEMPLATE_ID IN (SELECT LOT_TEMPLATE_ID FROM @_@SKU WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID) ";
            sql = sql.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@SKU_ID", skuID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetMandatoryLotAttributeInfo(DataBase dataBase, DbTransaction tran, string ownerID, string skuID, string whLoginID)
        {
            string sql = " SELECT PRODUCE_DATE_MANDATORY ,EXPIRY_DATE_MANDATORY ,LOT_ATTR01_MANDATORY ,LOT_ATTR02_MANDATORY ,LOT_ATTR03_MANDATORY ,LOT_ATTR04_MANDATORY ,LOT_ATTR05_MANDATORY ,LOT_ATTR06_MANDATORY ,LOT_ATTR07_MANDATORY ,LOT_ATTR08_MANDATORY ,LOT_ATTR09_MANDATORY ,LOT_ATTR10_MANDATORY FROM @_@LOT_TEMPLATE WHERE LOT_TEMPLATE_ID IN (SELECT LOT_TEMPLATE_ID FROM @_@SKU WHERE OWNER_ID = @OWNER_ID AND SKU_ID = @SKU_ID) ";
            sql = sql.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@SKU_ID", skuID) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
        }

        public ReceiptDetailInfo GetNextUncompletedReceiptDetailInfo(string receiptID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = string.Empty;
            if (this.CheckReceiptIsPalletized(receiptID, whLoginID))
            {
                sql = this.SQL_SELECT_ALL_RECEIPT_DETAIL_LPN.Replace("@_@", whLoginID) + " WHERE RD.RECEIPT_ID = @RECEIPT_ID AND RD.STATUS <> 'ReceivedComplete' ORDER BY RD.RECEIPT_LINE_ID ";
            }
            else
            {
                sql = this.SQL_SELECT_ALL_RECEIPT_DETAIL.Replace("@_@", whLoginID) + " WHERE RD.RECEIPT_ID = @RECEIPT_ID AND RD.STATUS <> 'ReceivedComplete' ORDER BY RD.RECEIPT_LINE_ID ";
            }
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            ReceiptDetailInfo receiptDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    receiptDetailInfo = new ReceiptDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["RECEIPT_ID"].ToString(), (reader["RECEIPT_LINE_ID"].ToString() != "") ? int.Parse(reader["RECEIPT_LINE_ID"].ToString()) : 0, reader["EXTERN_RECEIPT_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["PO_ID"].ToString(), int.Parse((reader["PO_LINE_ID"].ToString() == "") ? "0" : reader["PO_LINE_ID"].ToString()), reader["TARIFF_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["LPN_ID"].ToString(), reader["CASE_ID"].ToString(), reader["STATUS"].ToString(), (reader["RECEIVED_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIVED_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["RECEIVED_QTY"].ToString()) : 0M, (reader["REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["REJECTED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LOT"].ToString(), reader["TO_LPN_ID"].ToString(), reader["CONDITION_CODE"].ToString(), (reader["CASE_CNT"].ToString() != "") ? decimal.Parse(reader["CASE_CNT"].ToString()) : 0M, (reader["INNER_PACK"].ToString() != "") ? decimal.Parse(reader["INNER_PACK"].ToString()) : 0M, (reader["PALLET"].ToString() != "") ? decimal.Parse(reader["PALLET"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? float.Parse(reader["CUBE"].ToString()) : 0f, (reader["GROSS_WGT"].ToString() != "") ? float.Parse(reader["GROSS_WGT"].ToString()) : 0f, (reader["NET_WGT"].ToString() != "") ? float.Parse(reader["NET_WGT"].ToString()) : 0f, (reader["OTHER_UNIT1"].ToString() != "") ? float.Parse(reader["OTHER_UNIT1"].ToString()) : 0f, (reader["OTHER_UNIT2"].ToString() != "") ? float.Parse(reader["OTHER_UNIT2"].ToString()) : 0f, (reader["UNIT_PRICE"].ToString() != "") ? float.Parse(reader["UNIT_PRICE"].ToString()) : 0f, (reader["EXTENDED_PRICE"].ToString() != "") ? float.Parse(reader["EXTENDED_PRICE"].ToString()) : 0f, (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REASON_CODE"].ToString(), reader["PALLET_ID"].ToString(), reader["TYPE"].ToString(), reader["RETURN_TYPE"].ToString(), reader["RETURN_REASON"].ToString(), reader["DISPOSITION_TYPE"].ToString(), reader["DISPOSITION_CODE"].ToString(), reader["RETURN_CONDITION"].ToString(), reader["QC_REQUIRED"].ToString(), (reader["QC_INSPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_INSPECTED_QTY"].ToString()) : 0M, (reader["QC_REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_REJECTED_QTY"].ToString()) : 0M, reader["QC_REJ_REASON"].ToString(), reader["QC_STATUS"].ToString(), reader["QC_USER"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["RMA"].ToString(), reader["VENDOR_ID"].ToString(), reader["VENDOR_NAME"].ToString(), reader["MATCH_LOTTABLE"].ToString(), reader["CROSS_DOCK_ID"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["SKU_DESCR"].ToString(), reader["STATUS_D"].ToString(), reader["LPN_ID"].ToString());
                    ReceiptLotInfo receiptLot = new ReceiptLotInfo(reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : new DateTime(), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString());
                    receiptDetailInfo.ReceiptLot = receiptLot;
                }
            }
            return receiptDetailInfo;
        }

        public List<ReceiptPutawayInfo> GetPendingPutAwayTaskByTransID(string transID, string whLoginID)
        {
            string sql = this.SQL_GET_PENDIND_PA_TASK_BY_TRANS_ID.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TRANS_ID", transID) };
            List<ReceiptPutawayInfo> receiptPutawayInfos = new List<ReceiptPutawayInfo>();
            ReceiptPutawayInfo receiptPutawayInfo = null;
            IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras);
            while (reader.Read())
            {
                receiptPutawayInfo = new ReceiptPutawayInfo(reader["FROM_TRANS_ID"].ToString(), reader["DOC_ID"].ToString(), reader["DOC_LINE_ID"].ToString(), reader["SKU_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["FROM_LOC"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LPN_ID"].ToString(), reader["LOT"].ToString(), decimal.Parse((reader["QTY"].ToString() == "") ? "0" : reader["QTY"].ToString()), reader["HOLD_CODE"].ToString(), reader["UPDATED_BY"].ToString(), reader["WH_ID"].ToString(), reader["FROM_LPN_ID"].ToString(), reader["CASE_ID"].ToString());
                receiptPutawayInfos.Add(receiptPutawayInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return receiptPutawayInfos;
        }

        public List<ReceiptPutawayInfo> GetPendingPutAwayTaskByTransID(DataBase dataBase, DbTransaction tran, string transID, string whLoginID)
        {
            string sql = this.SQL_GET_PENDIND_PA_TASK_BY_TRANS_ID.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TRANS_ID", transID) };
            List<ReceiptPutawayInfo> receiptPutawayInfos = new List<ReceiptPutawayInfo>();
            ReceiptPutawayInfo receiptPutawayInfo = null;
            using (IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    receiptPutawayInfo = new ReceiptPutawayInfo(reader["FROM_TRANS_ID"].ToString(), reader["DOC_ID"].ToString(), reader["DOC_LINE_ID"].ToString(), reader["SKU_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["FROM_LOC"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LPN_ID"].ToString(), reader["LOT"].ToString(), decimal.Parse((reader["QTY"].ToString() == "") ? "0" : reader["QTY"].ToString()), reader["HOLD_CODE"].ToString(), reader["UPDATED_BY"].ToString(), reader["WH_ID"].ToString(), reader["FROM_LPN_ID"].ToString(), reader["CASE_ID"].ToString());
                    receiptPutawayInfos.Add(receiptPutawayInfo);
                }
            }
            return receiptPutawayInfos;
        }

        public PutawayDetailInfo GetPutawayDetailInfoByLpnID(string lpnID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_PUTAWAY_DETAIL_BY_LPN.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TO_LPN_ID", lpnID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            PutawayDetailInfo putawayDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    putawayDetailInfo = new PutawayDetailInfo(reader["TASK_DETAIL_ID"].ToString(), reader["RECEIPT_ID"].ToString(), reader["RECEIPT_LINE_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["DESCR"].ToString(), reader["LOT"].ToString(), reader["TO_LPN_ID"].ToString(), decimal.Parse((reader["QTY"].ToString() == "") ? "0" : reader["QTY"].ToString()), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), reader["FROM_LOC"].ToString(), reader["FROM_LPN_ID"].ToString(), reader["TO_LOC"].ToString(), reader["STATUS"].ToString(), reader["STATUS_D"].ToString(), string.IsNullOrEmpty(reader["FINAL_TO_LOC"].ToString()) ? reader["TO_LOC"].ToString() : reader["FINAL_TO_LOC"].ToString());
                }
            }
            return putawayDetailInfo;
        }

        public PutawayDetailInfo GetPutawayDetailInfoByLpnID(DataBase dataBase, DbTransaction tran, string lpnID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_PUTAWAY_DETAIL_BY_LPN.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TO_LPN_ID", lpnID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            PutawayDetailInfo putawayDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                putawayDetailInfo = new PutawayDetailInfo(reader["TASK_DETAIL_ID"].ToString(), reader["RECEIPT_ID"].ToString(), reader["RECEIPT_LINE_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["DESCR"].ToString(), reader["LOT"].ToString(), reader["TO_LPN_ID"].ToString(), decimal.Parse((reader["QTY"].ToString() == "") ? "0" : reader["QTY"].ToString()), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), reader["FROM_LOC"].ToString(), reader["FROM_LPN_ID"].ToString(), reader["TO_LOC"].ToString(), reader["STATUS"].ToString(), reader["STATUS_D"].ToString(), string.IsNullOrEmpty(reader["FINAL_TO_LOC"].ToString()) ? reader["TO_LOC"].ToString() : reader["FINAL_TO_LOC"].ToString());
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return putawayDetailInfo;
        }

        public List<PutawayDetailInfo> GetPutAwayDetailInfoListByID(string receiptID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_PUTAWAY_DETAIL.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<PutawayDetailInfo> putawayDetailInfos = new List<PutawayDetailInfo>();
            PutawayDetailInfo putawayDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    putawayDetailInfo = new PutawayDetailInfo(reader["TASK_DETAIL_ID"].ToString(), reader["RECEIPT_ID"].ToString(), reader["RECEIPT_LINE_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["DESCR"].ToString(), reader["LOT"].ToString(), reader["TO_LPN_ID"].ToString(), decimal.Parse((reader["QTY"].ToString() == "") ? "0" : reader["QTY"].ToString()), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), reader["FROM_LOC"].ToString(), reader["FROM_LPN_ID"].ToString(), reader["TO_LOC"].ToString(), reader["STATUS"].ToString(), reader["STATUS_D"].ToString(), string.IsNullOrEmpty(reader["FINAL_TO_LOC"].ToString()) ? reader["TO_LOC"].ToString() : reader["FINAL_TO_LOC"].ToString());
                    putawayDetailInfos.Add(putawayDetailInfo);
                }
            }
            return putawayDetailInfos;
        }

        public List<PutawayDetailInfo> GetPutAwayDetailInfoListByID(DataBase dataBase, DbTransaction tran, string receiptID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = this.SQL_SELECT_ALL_PUTAWAY_DETAIL.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<PutawayDetailInfo> putawayDetailInfos = new List<PutawayDetailInfo>();
            PutawayDetailInfo putawayDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                putawayDetailInfo = new PutawayDetailInfo(reader["TASK_DETAIL_ID"].ToString(), reader["RECEIPT_ID"].ToString(), reader["RECEIPT_LINE_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["DESCR"].ToString(), reader["LOT"].ToString(), reader["TO_LPN_ID"].ToString(), decimal.Parse((reader["QTY"].ToString() == "") ? "0" : reader["QTY"].ToString()), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), reader["FROM_LOC"].ToString(), reader["FROM_LPN_ID"].ToString(), reader["TO_LOC"].ToString(), reader["STATUS"].ToString(), reader["STATUS_D"].ToString(), string.IsNullOrEmpty(reader["FINAL_TO_LOC"].ToString()) ? reader["TO_LOC"].ToString() : reader["FINAL_TO_LOC"].ToString());
                putawayDetailInfos.Add(putawayDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return putawayDetailInfos;
        }

        public DataSet GetPutAwayLocByPaStrategy(DataBase dataBase, DbTransaction tran, string paStrategy, string toZone, string ownerID, string skuID, string whLoginID)
        {
            string sql = string.Empty;
            SqlParameter[] paras = null;
            switch (paStrategy)
            {
                case "10":
                    sql = this.SQL_SELECT_LOC_BY_SKU_LOC.Replace("@_@", whLoginID);
                    paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@SKU_ID", skuID) };
                    break;

                case "20":
                    sql = this.SQL_SELECT_LOC_BY_SKU_ZONE.Replace("@_@", whLoginID);
                    paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@SKU_ID", skuID) };
                    break;

                case "30":
                    sql = this.SQL_SELECT_LOC_BY_APPOINT_ZONE.Replace("@_@", whLoginID);
                    paras = new SqlParameter[] { new SqlParameter("@ZONE_ID", toZone) };
                    break;

                case "60":
                    sql = this.SQL_SELECT_LOC_BY_APPOINT_ZONE.Replace("@_@", whLoginID);
                    paras = new SqlParameter[] { new SqlParameter("@ZONE_ID", toZone) };
                    break;

                case "70":
                    sql = this.SQL_SELECT_LOC_BY_SKU_PICK_LOC.Replace("@_@", whLoginID);
                    paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@SKU_ID", skuID) };
                    break;

                case "80":
                    sql = this.SQL_SELECT_LOC_BY_SKU_CASE_LOC.Replace("@_@", whLoginID);
                    paras = new SqlParameter[] { new SqlParameter("@OWNER_ID", ownerID), new SqlParameter("@SKU_ID", skuID) };
                    break;
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
        }

        public ReceiptDetailInfo GetReceiptDetailByID(string receiptID, string receiptLineID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = string.Empty;
            if (this.CheckReceiptIsPalletized(receiptID, whLoginID))
            {
                sql = this.SQL_SELECT_ALL_RECEIPT_DETAIL_LPN.Replace("@_@", whLoginID) + " WHERE RD.RECEIPT_ID = @RECEIPT_ID AND RD.RECEIPT_LINE_ID = @RECEIPT_LINE_ID  ";
            }
            else
            {
                sql = this.SQL_SELECT_ALL_RECEIPT_DETAIL.Replace("@_@", whLoginID) + " WHERE RD.RECEIPT_ID = @RECEIPT_ID AND RD.RECEIPT_LINE_ID = @RECEIPT_LINE_ID  ";
            }
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID), new SqlParameter("@RECEIPT_LINE_ID", receiptLineID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            ReceiptDetailInfo receiptDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    receiptDetailInfo = new ReceiptDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["RECEIPT_ID"].ToString(), (reader["RECEIPT_LINE_ID"].ToString() != "") ? int.Parse(reader["RECEIPT_LINE_ID"].ToString()) : 0, reader["EXTERN_RECEIPT_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["PO_ID"].ToString(), int.Parse((reader["PO_LINE_ID"].ToString() == "") ? "0" : reader["PO_LINE_ID"].ToString()), reader["TARIFF_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["LPN_ID"].ToString(), reader["CASE_ID"].ToString(), reader["STATUS"].ToString(), (reader["RECEIVED_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIVED_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["RECEIVED_QTY"].ToString()) : 0M, (reader["REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["REJECTED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LOT"].ToString(), reader["TO_LPN_ID"].ToString(), reader["CONDITION_CODE"].ToString(), (reader["CASE_CNT"].ToString() != "") ? decimal.Parse(reader["CASE_CNT"].ToString()) : 0M, (reader["INNER_PACK"].ToString() != "") ? decimal.Parse(reader["INNER_PACK"].ToString()) : 0M, (reader["PALLET"].ToString() != "") ? decimal.Parse(reader["PALLET"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? float.Parse(reader["CUBE"].ToString()) : 0f, (reader["GROSS_WGT"].ToString() != "") ? float.Parse(reader["GROSS_WGT"].ToString()) : 0f, (reader["NET_WGT"].ToString() != "") ? float.Parse(reader["NET_WGT"].ToString()) : 0f, (reader["OTHER_UNIT1"].ToString() != "") ? float.Parse(reader["OTHER_UNIT1"].ToString()) : 0f, (reader["OTHER_UNIT2"].ToString() != "") ? float.Parse(reader["OTHER_UNIT2"].ToString()) : 0f, (reader["UNIT_PRICE"].ToString() != "") ? float.Parse(reader["UNIT_PRICE"].ToString()) : 0f, (reader["EXTENDED_PRICE"].ToString() != "") ? float.Parse(reader["EXTENDED_PRICE"].ToString()) : 0f, (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REASON_CODE"].ToString(), reader["PALLET_ID"].ToString(), reader["TYPE"].ToString(), reader["RETURN_TYPE"].ToString(), reader["RETURN_REASON"].ToString(), reader["DISPOSITION_TYPE"].ToString(), reader["DISPOSITION_CODE"].ToString(), reader["RETURN_CONDITION"].ToString(), reader["QC_REQUIRED"].ToString(), (reader["QC_INSPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_INSPECTED_QTY"].ToString()) : 0M, (reader["QC_REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_REJECTED_QTY"].ToString()) : 0M, reader["QC_REJ_REASON"].ToString(), reader["QC_STATUS"].ToString(), reader["QC_USER"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["RMA"].ToString(), reader["VENDOR_ID"].ToString(), reader["VENDOR_NAME"].ToString(), reader["MATCH_LOTTABLE"].ToString(), reader["CROSS_DOCK_ID"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["SKU_DESCR"].ToString(), reader["STATUS_D"].ToString(), reader["LPN_ID"].ToString());
                    ReceiptLotInfo receiptLot = new ReceiptLotInfo(reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : new DateTime(), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString());
                    receiptDetailInfo.ReceiptLot = receiptLot;
                }
            }
            return receiptDetailInfo;
        }

        public ReceiptDetailInfo GetReceiptDetailByID(DataBase dataBase, DbTransaction tran, string receiptID, string receiptLineID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = string.Empty;
            if (this.CheckReceiptIsPalletized(dataBase, tran, receiptID, whLoginID))
            {
                sql = this.SQL_SELECT_ALL_RECEIPT_DETAIL_LPN.Replace("@_@", whLoginID) + " WHERE RD.RECEIPT_ID = @RECEIPT_ID AND RD.RECEIPT_LINE_ID = @RECEIPT_LINE_ID  ";
            }
            else
            {
                sql = this.SQL_SELECT_ALL_RECEIPT_DETAIL.Replace("@_@", whLoginID) + " WHERE RD.RECEIPT_ID = @RECEIPT_ID AND RD.RECEIPT_LINE_ID = @RECEIPT_LINE_ID  ";
            }
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID), new SqlParameter("@RECEIPT_LINE_ID", receiptLineID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            ReceiptDetailInfo receiptDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                receiptDetailInfo = new ReceiptDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["RECEIPT_ID"].ToString(), (reader["RECEIPT_LINE_ID"].ToString() != "") ? int.Parse(reader["RECEIPT_LINE_ID"].ToString()) : 0, reader["EXTERN_RECEIPT_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["PO_ID"].ToString(), int.Parse((reader["PO_LINE_ID"].ToString() == "") ? "0" : reader["PO_LINE_ID"].ToString()), reader["TARIFF_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["LPN_ID"].ToString(), reader["CASE_ID"].ToString(), reader["STATUS"].ToString(), (reader["RECEIVED_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIVED_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["RECEIVED_QTY"].ToString()) : 0M, (reader["REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["REJECTED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LOT"].ToString(), reader["TO_LPN_ID"].ToString(), reader["CONDITION_CODE"].ToString(), (reader["CASE_CNT"].ToString() != "") ? decimal.Parse(reader["CASE_CNT"].ToString()) : 0M, (reader["INNER_PACK"].ToString() != "") ? decimal.Parse(reader["INNER_PACK"].ToString()) : 0M, (reader["PALLET"].ToString() != "") ? decimal.Parse(reader["PALLET"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? float.Parse(reader["CUBE"].ToString()) : 0f, (reader["GROSS_WGT"].ToString() != "") ? float.Parse(reader["GROSS_WGT"].ToString()) : 0f, (reader["NET_WGT"].ToString() != "") ? float.Parse(reader["NET_WGT"].ToString()) : 0f, (reader["OTHER_UNIT1"].ToString() != "") ? float.Parse(reader["OTHER_UNIT1"].ToString()) : 0f, (reader["OTHER_UNIT2"].ToString() != "") ? float.Parse(reader["OTHER_UNIT2"].ToString()) : 0f, (reader["UNIT_PRICE"].ToString() != "") ? float.Parse(reader["UNIT_PRICE"].ToString()) : 0f, (reader["EXTENDED_PRICE"].ToString() != "") ? float.Parse(reader["EXTENDED_PRICE"].ToString()) : 0f, (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REASON_CODE"].ToString(), reader["PALLET_ID"].ToString(), reader["TYPE"].ToString(), reader["RETURN_TYPE"].ToString(), reader["RETURN_REASON"].ToString(), reader["DISPOSITION_TYPE"].ToString(), reader["DISPOSITION_CODE"].ToString(), reader["RETURN_CONDITION"].ToString(), reader["QC_REQUIRED"].ToString(), (reader["QC_INSPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_INSPECTED_QTY"].ToString()) : 0M, (reader["QC_REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_REJECTED_QTY"].ToString()) : 0M, reader["QC_REJ_REASON"].ToString(), reader["QC_STATUS"].ToString(), reader["QC_USER"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["RMA"].ToString(), reader["VENDOR_ID"].ToString(), reader["VENDOR_NAME"].ToString(), reader["MATCH_LOTTABLE"].ToString(), reader["CROSS_DOCK_ID"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["SKU_DESCR"].ToString(), reader["STATUS_D"].ToString(), reader["LPN_ID"].ToString());
                ReceiptLotInfo receiptLot = new ReceiptLotInfo(reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : new DateTime(), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString());
                receiptDetailInfo.ReceiptLot = receiptLot;
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return receiptDetailInfo;
        }

        public DataSet GetReceiptDetailByPagerQueryList(List<string> sqlWhere, ReceiptDetailQueryEntity receiptdetailQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_RECEIPT_DETAIL.Replace("@_@", whLoginID) + " WHERE RD.RECEIPT_ID = @RECEIPT_ID";
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                temp = temp + " AND " + sqlWhere[i].ToString();
            }
            temp = PagingHelper.GetPagingSQL(temp, receiptdetailQuery.CurrentPage, receiptdetailQuery.PageSize, receiptdetailQuery.SortField, receiptdetailQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''").Replace("@RECEIPT_ID", "''" + receiptdetailQuery.ReceiptID + "''");
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public ReceiptDetailInfo GetReceiptDetailByPoID(DataBase dataBase, DbTransaction tran, string poID, string poLineID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = string.Empty;
            sql = this.SQL_SELECT_ALL_RECEIPT_DETAIL.Replace("@_@", whLoginID) + " WHERE RD.PO_ID = @PO_ID AND RD.PO_LINE_ID = @PO_LINE_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PO_ID", poID), new SqlParameter("@PO_LINE_ID", poLineID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            ReceiptDetailInfo receiptDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            if (reader.Read())
            {
                DateTime CS00003;
                receiptDetailInfo = new ReceiptDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["RECEIPT_ID"].ToString(), (reader["RECEIPT_LINE_ID"].ToString() != "") ? int.Parse(reader["RECEIPT_LINE_ID"].ToString()) : 0, reader["EXTERN_RECEIPT_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["PO_ID"].ToString(), int.Parse((reader["PO_LINE_ID"].ToString() == "") ? "0" : reader["PO_LINE_ID"].ToString()), reader["TARIFF_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["LPN_ID"].ToString(), reader["CASE_ID"].ToString(), reader["STATUS"].ToString(), (reader["RECEIVED_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIVED_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["RECEIVED_QTY"].ToString()) : 0M, (reader["REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["REJECTED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LOT"].ToString(), reader["TO_LPN_ID"].ToString(), reader["CONDITION_CODE"].ToString(), (reader["CASE_CNT"].ToString() != "") ? decimal.Parse(reader["CASE_CNT"].ToString()) : 0M, (reader["INNER_PACK"].ToString() != "") ? decimal.Parse(reader["INNER_PACK"].ToString()) : 0M, (reader["PALLET"].ToString() != "") ? decimal.Parse(reader["PALLET"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? float.Parse(reader["CUBE"].ToString()) : 0f, (reader["GROSS_WGT"].ToString() != "") ? float.Parse(reader["GROSS_WGT"].ToString()) : 0f, (reader["NET_WGT"].ToString() != "") ? float.Parse(reader["NET_WGT"].ToString()) : 0f, (reader["OTHER_UNIT1"].ToString() != "") ? float.Parse(reader["OTHER_UNIT1"].ToString()) : 0f, (reader["OTHER_UNIT2"].ToString() != "") ? float.Parse(reader["OTHER_UNIT2"].ToString()) : 0f, (reader["UNIT_PRICE"].ToString() != "") ? float.Parse(reader["UNIT_PRICE"].ToString()) : 0f, (reader["EXTENDED_PRICE"].ToString() != "") ? float.Parse(reader["EXTENDED_PRICE"].ToString()) : 0f, (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REASON_CODE"].ToString(), reader["PALLET_ID"].ToString(), reader["TYPE"].ToString(), reader["RETURN_TYPE"].ToString(), reader["RETURN_REASON"].ToString(), reader["DISPOSITION_TYPE"].ToString(), reader["DISPOSITION_CODE"].ToString(), reader["RETURN_CONDITION"].ToString(), reader["QC_REQUIRED"].ToString(), (reader["QC_INSPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_INSPECTED_QTY"].ToString()) : 0M, (reader["QC_REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_REJECTED_QTY"].ToString()) : 0M, reader["QC_REJ_REASON"].ToString(), reader["QC_STATUS"].ToString(), reader["QC_USER"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["RMA"].ToString(), reader["VENDOR_ID"].ToString(), reader["VENDOR_NAME"].ToString(), reader["MATCH_LOTTABLE"].ToString(), reader["CROSS_DOCK_ID"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00003 = new DateTime()), reader["SKU_DESCR"].ToString(), reader["STATUS_D"].ToString(), reader["LPN_ID"].ToString());
                ReceiptLotInfo receiptLot = new ReceiptLotInfo(reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : new DateTime(), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString());
                receiptDetailInfo.ReceiptLot = receiptLot;
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return receiptDetailInfo;
        }

        public DataSet GetReceiptDetailByQueryList(List<string> sqlWhere, ReceiptDetailQueryEntity receiptdetailQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_RECEIPT_DETAIL.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!receiptdetailQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, receiptdetailQuery.CurrentPage, receiptdetailQuery.PageSize, receiptdetailQuery.SortField, receiptdetailQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetReceiptDetailByQueryList(DataBase dataBase, DbTransaction tran, List<string> sqlWhere, ReceiptDetailQueryEntity receiptdetailQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = this.SQL_SELECT_ALL_RECEIPT_DETAIL.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!receiptdetailQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, receiptdetailQuery.CurrentPage, receiptdetailQuery.PageSize, receiptdetailQuery.SortField, receiptdetailQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return dataBase.ExecuteDataSet(tran, CommandType.Text, temp);
        }

        public string GetReceiptDetailInfoBySkuIDDescCreatedDate(string ownerID, string skuID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = string.Empty;
            ownerID = "'" + ownerID.Replace(",", "','") + "'";
            string strOwnerID = string.Empty;
            sql = this.SQL_SELECT_ALL_RECEIPT_DETAIL.Replace("@_@", whLoginID) + " WHERE RD.SKU_ID = @SKU_ID AND RD.OWNER_ID IN(" + ownerID + ") ORDER BY RD.CREATED_DATE DESC ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@SKU_ID", skuID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    strOwnerID = reader["OWNER_ID"].ToString();
                }
            }
            return strOwnerID;
        }

        public List<ReceiptDetailInfo> GetReceiptDetailListByID(string receiptID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = string.Empty;
            sql = this.SQL_SELECT_ALL_RECEIPT_DETAIL.Replace("@_@", whLoginID) + " WHERE RD.RECEIPT_ID = @RECEIPT_ID ORDER BY RECEIPT_LINE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<ReceiptDetailInfo> receiptDetailInfos = new List<ReceiptDetailInfo>();
            ReceiptDetailInfo receiptDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    receiptDetailInfo = new ReceiptDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["RECEIPT_ID"].ToString(), (reader["RECEIPT_LINE_ID"].ToString() != "") ? int.Parse(reader["RECEIPT_LINE_ID"].ToString()) : 0, reader["EXTERN_RECEIPT_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["PO_ID"].ToString(), int.Parse((reader["PO_LINE_ID"].ToString() == "") ? "0" : reader["PO_LINE_ID"].ToString()), reader["TARIFF_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["LPN_ID"].ToString(), reader["CASE_ID"].ToString(), reader["STATUS"].ToString(), (reader["RECEIVED_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIVED_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["RECEIVED_QTY"].ToString()) : 0M, (reader["REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["REJECTED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LOT"].ToString(), reader["TO_LPN_ID"].ToString(), reader["CONDITION_CODE"].ToString(), (reader["CASE_CNT"].ToString() != "") ? decimal.Parse(reader["CASE_CNT"].ToString()) : 0M, (reader["INNER_PACK"].ToString() != "") ? decimal.Parse(reader["INNER_PACK"].ToString()) : 0M, (reader["PALLET"].ToString() != "") ? decimal.Parse(reader["PALLET"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? float.Parse(reader["CUBE"].ToString()) : 0f, (reader["GROSS_WGT"].ToString() != "") ? float.Parse(reader["GROSS_WGT"].ToString()) : 0f, (reader["NET_WGT"].ToString() != "") ? float.Parse(reader["NET_WGT"].ToString()) : 0f, (reader["OTHER_UNIT1"].ToString() != "") ? float.Parse(reader["OTHER_UNIT1"].ToString()) : 0f, (reader["OTHER_UNIT2"].ToString() != "") ? float.Parse(reader["OTHER_UNIT2"].ToString()) : 0f, (reader["UNIT_PRICE"].ToString() != "") ? float.Parse(reader["UNIT_PRICE"].ToString()) : 0f, (reader["EXTENDED_PRICE"].ToString() != "") ? float.Parse(reader["EXTENDED_PRICE"].ToString()) : 0f, (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REASON_CODE"].ToString(), reader["PALLET_ID"].ToString(), reader["TYPE"].ToString(), reader["RETURN_TYPE"].ToString(), reader["RETURN_REASON"].ToString(), reader["DISPOSITION_TYPE"].ToString(), reader["DISPOSITION_CODE"].ToString(), reader["RETURN_CONDITION"].ToString(), reader["QC_REQUIRED"].ToString(), (reader["QC_INSPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_INSPECTED_QTY"].ToString()) : 0M, (reader["QC_REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_REJECTED_QTY"].ToString()) : 0M, reader["QC_REJ_REASON"].ToString(), reader["QC_STATUS"].ToString(), reader["QC_USER"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["RMA"].ToString(), reader["VENDOR_ID"].ToString(), reader["VENDOR_NAME"].ToString(), reader["MATCH_LOTTABLE"].ToString(), reader["CROSS_DOCK_ID"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["SKU_DESCR"].ToString(), reader["STATUS_D"].ToString(), reader["LPN_ID"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString());
                    ReceiptLotInfo receiptLot = new ReceiptLotInfo(reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString());
                    receiptDetailInfo.ReceiptLot = receiptLot;
                    receiptDetailInfos.Add(receiptDetailInfo);
                }
            }
            return receiptDetailInfos;
        }

        public List<ReceiptDetailInfo> GetReceiptDetailListByID(DataBase dataBase, DbTransaction tran, string receiptID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = string.Empty;
            sql = this.SQL_SELECT_ALL_RECEIPT_DETAIL.Replace("@_@", whLoginID) + " WHERE RD.RECEIPT_ID = @RECEIPT_ID ORDER BY RECEIPT_LINE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<ReceiptDetailInfo> receiptDetailInfos = new List<ReceiptDetailInfo>();
            ReceiptDetailInfo receiptDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                receiptDetailInfo = new ReceiptDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["RECEIPT_ID"].ToString(), (reader["RECEIPT_LINE_ID"].ToString() != "") ? int.Parse(reader["RECEIPT_LINE_ID"].ToString()) : 0, reader["EXTERN_RECEIPT_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["PO_ID"].ToString(), int.Parse((reader["PO_LINE_ID"].ToString() == "") ? "0" : reader["PO_LINE_ID"].ToString()), reader["TARIFF_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["LPN_ID"].ToString(), reader["CASE_ID"].ToString(), reader["STATUS"].ToString(), (reader["RECEIVED_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIVED_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["RECEIVED_QTY"].ToString()) : 0M, (reader["REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["REJECTED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LOT"].ToString(), reader["TO_LPN_ID"].ToString(), reader["CONDITION_CODE"].ToString(), (reader["CASE_CNT"].ToString() != "") ? decimal.Parse(reader["CASE_CNT"].ToString()) : 0M, (reader["INNER_PACK"].ToString() != "") ? decimal.Parse(reader["INNER_PACK"].ToString()) : 0M, (reader["PALLET"].ToString() != "") ? decimal.Parse(reader["PALLET"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? float.Parse(reader["CUBE"].ToString()) : 0f, (reader["GROSS_WGT"].ToString() != "") ? float.Parse(reader["GROSS_WGT"].ToString()) : 0f, (reader["NET_WGT"].ToString() != "") ? float.Parse(reader["NET_WGT"].ToString()) : 0f, (reader["OTHER_UNIT1"].ToString() != "") ? float.Parse(reader["OTHER_UNIT1"].ToString()) : 0f, (reader["OTHER_UNIT2"].ToString() != "") ? float.Parse(reader["OTHER_UNIT2"].ToString()) : 0f, (reader["UNIT_PRICE"].ToString() != "") ? float.Parse(reader["UNIT_PRICE"].ToString()) : 0f, (reader["EXTENDED_PRICE"].ToString() != "") ? float.Parse(reader["EXTENDED_PRICE"].ToString()) : 0f, (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REASON_CODE"].ToString(), reader["PALLET_ID"].ToString(), reader["TYPE"].ToString(), reader["RETURN_TYPE"].ToString(), reader["RETURN_REASON"].ToString(), reader["DISPOSITION_TYPE"].ToString(), reader["DISPOSITION_CODE"].ToString(), reader["RETURN_CONDITION"].ToString(), reader["QC_REQUIRED"].ToString(), (reader["QC_INSPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_INSPECTED_QTY"].ToString()) : 0M, (reader["QC_REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_REJECTED_QTY"].ToString()) : 0M, reader["QC_REJ_REASON"].ToString(), reader["QC_STATUS"].ToString(), reader["QC_USER"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["RMA"].ToString(), reader["VENDOR_ID"].ToString(), reader["VENDOR_NAME"].ToString(), reader["MATCH_LOTTABLE"].ToString(), reader["CROSS_DOCK_ID"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["SKU_DESCR"].ToString(), reader["STATUS_D"].ToString(), reader["LPN_ID"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString());
                ReceiptLotInfo receiptLot = new ReceiptLotInfo(reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString());
                receiptDetailInfo.ReceiptLot = receiptLot;
                receiptDetailInfos.Add(receiptDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return receiptDetailInfos;
        }

        public List<ReceiptDetailInfo> GetReceiptDetailListByPoID(DataBase dataBase, DbTransaction tran, string poID, string poLineID, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = string.Empty;
            sql = this.SQL_SELECT_ALL_RECEIPT_DETAIL.Replace("@_@", whLoginID) + " WHERE RD.PO_ID = @PO_ID AND RD.PO_LINE_ID = @PO_LINE_ID  ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@PO_ID", poID), new SqlParameter("@PO_LINE_ID", poLineID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<ReceiptDetailInfo> receiptDetailList = new List<ReceiptDetailInfo>();
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                ReceiptDetailInfo receiptDetailInfo = new ReceiptDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["RECEIPT_ID"].ToString(), (reader["RECEIPT_LINE_ID"].ToString() != "") ? int.Parse(reader["RECEIPT_LINE_ID"].ToString()) : 0, reader["EXTERN_RECEIPT_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["PO_ID"].ToString(), int.Parse((reader["PO_LINE_ID"].ToString() == "") ? "0" : reader["PO_LINE_ID"].ToString()), reader["TARIFF_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["LPN_ID"].ToString(), reader["CASE_ID"].ToString(), reader["STATUS"].ToString(), (reader["RECEIVED_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIVED_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["RECEIVED_QTY"].ToString()) : 0M, (reader["REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["REJECTED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LOT"].ToString(), reader["TO_LPN_ID"].ToString(), reader["CONDITION_CODE"].ToString(), (reader["CASE_CNT"].ToString() != "") ? decimal.Parse(reader["CASE_CNT"].ToString()) : 0M, (reader["INNER_PACK"].ToString() != "") ? decimal.Parse(reader["INNER_PACK"].ToString()) : 0M, (reader["PALLET"].ToString() != "") ? decimal.Parse(reader["PALLET"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? float.Parse(reader["CUBE"].ToString()) : 0f, (reader["GROSS_WGT"].ToString() != "") ? float.Parse(reader["GROSS_WGT"].ToString()) : 0f, (reader["NET_WGT"].ToString() != "") ? float.Parse(reader["NET_WGT"].ToString()) : 0f, (reader["OTHER_UNIT1"].ToString() != "") ? float.Parse(reader["OTHER_UNIT1"].ToString()) : 0f, (reader["OTHER_UNIT2"].ToString() != "") ? float.Parse(reader["OTHER_UNIT2"].ToString()) : 0f, (reader["UNIT_PRICE"].ToString() != "") ? float.Parse(reader["UNIT_PRICE"].ToString()) : 0f, (reader["EXTENDED_PRICE"].ToString() != "") ? float.Parse(reader["EXTENDED_PRICE"].ToString()) : 0f, (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REASON_CODE"].ToString(), reader["PALLET_ID"].ToString(), reader["TYPE"].ToString(), reader["RETURN_TYPE"].ToString(), reader["RETURN_REASON"].ToString(), reader["DISPOSITION_TYPE"].ToString(), reader["DISPOSITION_CODE"].ToString(), reader["RETURN_CONDITION"].ToString(), reader["QC_REQUIRED"].ToString(), (reader["QC_INSPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_INSPECTED_QTY"].ToString()) : 0M, (reader["QC_REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_REJECTED_QTY"].ToString()) : 0M, reader["QC_REJ_REASON"].ToString(), reader["QC_STATUS"].ToString(), reader["QC_USER"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["RMA"].ToString(), reader["VENDOR_ID"].ToString(), reader["VENDOR_NAME"].ToString(), reader["MATCH_LOTTABLE"].ToString(), reader["CROSS_DOCK_ID"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["SKU_DESCR"].ToString(), reader["STATUS_D"].ToString(), reader["LPN_ID"].ToString());
                ReceiptLotInfo receiptLot = new ReceiptLotInfo(reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString());
                receiptDetailInfo.ReceiptLot = receiptLot;
                receiptDetailList.Add(receiptDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return receiptDetailList;
        }

        public List<ReceiptDetailInfo> GetReceiptDetailListByWhere(string receiptID, string where, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = string.Empty;
            sql = this.SQL_SELECT_ALL_RECEIPT_DETAIL.Replace("@_@", whLoginID) + " WHERE RD.RECEIPT_ID = @RECEIPT_ID AND " + where + " ORDER BY RECEIPT_LINE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<ReceiptDetailInfo> receiptDetailInfos = new List<ReceiptDetailInfo>();
            ReceiptDetailInfo receiptDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    receiptDetailInfo = new ReceiptDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["RECEIPT_ID"].ToString(), (reader["RECEIPT_LINE_ID"].ToString() != "") ? int.Parse(reader["RECEIPT_LINE_ID"].ToString()) : 0, reader["EXTERN_RECEIPT_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["PO_ID"].ToString(), int.Parse((reader["PO_LINE_ID"].ToString() == "") ? "0" : reader["PO_LINE_ID"].ToString()), reader["TARIFF_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["LPN_ID"].ToString(), reader["CASE_ID"].ToString(), reader["STATUS"].ToString(), (reader["RECEIVED_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIVED_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["RECEIVED_QTY"].ToString()) : 0M, (reader["REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["REJECTED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LOT"].ToString(), reader["TO_LPN_ID"].ToString(), reader["CONDITION_CODE"].ToString(), (reader["CASE_CNT"].ToString() != "") ? decimal.Parse(reader["CASE_CNT"].ToString()) : 0M, (reader["INNER_PACK"].ToString() != "") ? decimal.Parse(reader["INNER_PACK"].ToString()) : 0M, (reader["PALLET"].ToString() != "") ? decimal.Parse(reader["PALLET"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? float.Parse(reader["CUBE"].ToString()) : 0f, (reader["GROSS_WGT"].ToString() != "") ? float.Parse(reader["GROSS_WGT"].ToString()) : 0f, (reader["NET_WGT"].ToString() != "") ? float.Parse(reader["NET_WGT"].ToString()) : 0f, (reader["OTHER_UNIT1"].ToString() != "") ? float.Parse(reader["OTHER_UNIT1"].ToString()) : 0f, (reader["OTHER_UNIT2"].ToString() != "") ? float.Parse(reader["OTHER_UNIT2"].ToString()) : 0f, (reader["UNIT_PRICE"].ToString() != "") ? float.Parse(reader["UNIT_PRICE"].ToString()) : 0f, (reader["EXTENDED_PRICE"].ToString() != "") ? float.Parse(reader["EXTENDED_PRICE"].ToString()) : 0f, (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REASON_CODE"].ToString(), reader["PALLET_ID"].ToString(), reader["TYPE"].ToString(), reader["RETURN_TYPE"].ToString(), reader["RETURN_REASON"].ToString(), reader["DISPOSITION_TYPE"].ToString(), reader["DISPOSITION_CODE"].ToString(), reader["RETURN_CONDITION"].ToString(), reader["QC_REQUIRED"].ToString(), (reader["QC_INSPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_INSPECTED_QTY"].ToString()) : 0M, (reader["QC_REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_REJECTED_QTY"].ToString()) : 0M, reader["QC_REJ_REASON"].ToString(), reader["QC_STATUS"].ToString(), reader["QC_USER"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["RMA"].ToString(), reader["VENDOR_ID"].ToString(), reader["VENDOR_NAME"].ToString(), reader["MATCH_LOTTABLE"].ToString(), reader["CROSS_DOCK_ID"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["SKU_DESCR"].ToString(), reader["STATUS_D"].ToString(), reader["LPN_ID"].ToString());
                    ReceiptLotInfo receiptLot = new ReceiptLotInfo(reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString());
                    receiptDetailInfo.ReceiptLot = receiptLot;
                    receiptDetailInfos.Add(receiptDetailInfo);
                }
            }
            return receiptDetailInfos;
        }

        public List<ReceiptDetailInfo> GetReceiptDetailListByWhere(DataBase dataBase, DbTransaction tran, string receiptID, string where, string whLoginID, PubParasInfo pubParasInfo)
        {
            string sql = string.Empty;
            sql = this.SQL_SELECT_ALL_RECEIPT_DETAIL.Replace("@_@", whLoginID) + " WHERE RD.RECEIPT_ID = @RECEIPT_ID AND " + where + " ORDER BY RECEIPT_LINE_ID ";
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID), new SqlParameter("@LANGUAGE", pubParasInfo.UserLanguage), new SqlParameter("@USER_ID", pubParasInfo.UserID) };
            List<ReceiptDetailInfo> receiptDetailInfos = new List<ReceiptDetailInfo>();
            ReceiptDetailInfo receiptDetailInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                receiptDetailInfo = new ReceiptDetailInfo((reader["ROWID"].ToString() != "") ? int.Parse(reader["ROWID"].ToString()) : 0, reader["WH_ID"].ToString(), reader["RECEIPT_ID"].ToString(), (reader["RECEIPT_LINE_ID"].ToString() != "") ? int.Parse(reader["RECEIPT_LINE_ID"].ToString()) : 0, reader["EXTERN_RECEIPT_ID"].ToString(), reader["EXTERN_LINE_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["PO_ID"].ToString(), int.Parse((reader["PO_LINE_ID"].ToString() == "") ? "0" : reader["PO_LINE_ID"].ToString()), reader["TARIFF_ID"].ToString(), reader["SKU_ID"].ToString(), reader["ALT_SKU_ID"].ToString(), reader["LPN_ID"].ToString(), reader["CASE_ID"].ToString(), reader["STATUS"].ToString(), (reader["RECEIVED_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIVED_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["EXPECTED_QTY"].ToString()) : 0M, (reader["ADJUSTED_QTY"].ToString() != "") ? decimal.Parse(reader["ADJUSTED_QTY"].ToString()) : 0M, (reader["RECEIVED_QTY"].ToString() != "") ? decimal.Parse(reader["RECEIVED_QTY"].ToString()) : 0M, (reader["REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["REJECTED_QTY"].ToString()) : 0M, reader["UOM_ID"].ToString(), reader["PACK_ID"].ToString(), reader["TO_LOC"].ToString(), reader["TO_LOT"].ToString(), reader["TO_LPN_ID"].ToString(), reader["CONDITION_CODE"].ToString(), (reader["CASE_CNT"].ToString() != "") ? decimal.Parse(reader["CASE_CNT"].ToString()) : 0M, (reader["INNER_PACK"].ToString() != "") ? decimal.Parse(reader["INNER_PACK"].ToString()) : 0M, (reader["PALLET"].ToString() != "") ? decimal.Parse(reader["PALLET"].ToString()) : 0M, (reader["CUBE"].ToString() != "") ? float.Parse(reader["CUBE"].ToString()) : 0f, (reader["GROSS_WGT"].ToString() != "") ? float.Parse(reader["GROSS_WGT"].ToString()) : 0f, (reader["NET_WGT"].ToString() != "") ? float.Parse(reader["NET_WGT"].ToString()) : 0f, (reader["OTHER_UNIT1"].ToString() != "") ? float.Parse(reader["OTHER_UNIT1"].ToString()) : 0f, (reader["OTHER_UNIT2"].ToString() != "") ? float.Parse(reader["OTHER_UNIT2"].ToString()) : 0f, (reader["UNIT_PRICE"].ToString() != "") ? float.Parse(reader["UNIT_PRICE"].ToString()) : 0f, (reader["EXTENDED_PRICE"].ToString() != "") ? float.Parse(reader["EXTENDED_PRICE"].ToString()) : 0f, (reader["EFFECTIVE_DATE"].ToString() != "") ? DateTime.Parse(reader["EFFECTIVE_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UDF1"].ToString(), reader["UDF2"].ToString(), reader["UDF3"].ToString(), reader["UDF4"].ToString(), reader["UDF5"].ToString(), reader["UDF6"].ToString(), reader["UDF7"].ToString(), reader["UDF8"].ToString(), reader["UDF9"].ToString(), reader["UDF10"].ToString(), reader["REASON_CODE"].ToString(), reader["PALLET_ID"].ToString(), reader["TYPE"].ToString(), reader["RETURN_TYPE"].ToString(), reader["RETURN_REASON"].ToString(), reader["DISPOSITION_TYPE"].ToString(), reader["DISPOSITION_CODE"].ToString(), reader["RETURN_CONDITION"].ToString(), reader["QC_REQUIRED"].ToString(), (reader["QC_INSPECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_INSPECTED_QTY"].ToString()) : 0M, (reader["QC_REJECTED_QTY"].ToString() != "") ? decimal.Parse(reader["QC_REJECTED_QTY"].ToString()) : 0M, reader["QC_REJ_REASON"].ToString(), reader["QC_STATUS"].ToString(), reader["QC_USER"].ToString(), reader["QC_AUTO_ADJUST"].ToString(), reader["RMA"].ToString(), reader["VENDOR_ID"].ToString(), reader["VENDOR_NAME"].ToString(), reader["MATCH_LOTTABLE"].ToString(), reader["CROSS_DOCK_ID"].ToString(), reader["REMARK"].ToString(), (byte[]) reader["TS"], reader["CREATED_BY"].ToString(), (reader["CREATED_DATE"].ToString() != "") ? DateTime.Parse(reader["CREATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["UPDATED_BY"].ToString(), (reader["UPDATED_DATE"].ToString() != "") ? DateTime.Parse(reader["UPDATED_DATE"].ToString()) : (CS00002 = new DateTime()), reader["SKU_DESCR"].ToString(), reader["STATUS_D"].ToString(), reader["LPN_ID"].ToString());
                ReceiptLotInfo receiptLot = new ReceiptLotInfo(reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString());
                receiptDetailInfo.ReceiptLot = receiptLot;
                receiptDetailInfos.Add(receiptDetailInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return receiptDetailInfos;
        }

        public DataSet GetReceiptDetailTotalExpectedAndReceivedQty(string receiptID, string whLoginID)
        {
            string sql = this.SQL_GET_TOTAL_EXPECTED_QTY_AND_RECEIVED_QTY.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public DataSet GetReceiptDetailTotalExpectedAndReceivedQty(DataBase dataBase, DbTransaction tran, string receiptID, string whLoginID)
        {
            string sql = this.SQL_GET_TOTAL_EXPECTED_QTY_AND_RECEIVED_QTY.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
        }

        public List<ReceiptHistoryInfo> GetReceiptHistoryInfoListByID(string receiptID, string whLoginID)
        {
            string where = "WHERE IT.TRANS_TYPE = 'IN' AND SOURCE_TRANS_TYPE = 'ReceiptDetailAdd' AND IT.STATUS = 'OK' AND IT.RECEIPT_ID = @RECEIPT_ID";
            string sql = this.SQL_SELECT_ALL_RECEIPT_HISTORY_LOT.Replace("@CONDITION@", where).Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID) };
            List<ReceiptHistoryInfo> receiptHistoryInfos = new List<ReceiptHistoryInfo>();
            ReceiptHistoryInfo receiptHistoryInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    DateTime CS00002;
                    receiptHistoryInfo = new ReceiptHistoryInfo(reader["TRANS_ID"].ToString(), reader["RECEIPT_ID"].ToString(), reader["RECEIPT_LINE_ID"].ToString(), reader["SKU_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["VENDOR_ID"].ToString(), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), reader["TO_LPN_ID"].ToString(), reader["LOT"].ToString(), reader["TO_LOC"].ToString(), decimal.Parse((reader["QTY"].ToString() == "") ? "0" : reader["QTY"].ToString()), reader["HOLD_CODE"].ToString(), decimal.Parse((reader["COMPLETED_QTY"].ToString() == "") ? "0" : reader["COMPLETED_QTY"].ToString()), decimal.Parse((reader["REMAIN_QTY"].ToString() == "") ? "0" : reader["REMAIN_QTY"].ToString()), decimal.Parse((reader["ALLOCATED_QTY"].ToString() == "") ? "0" : reader["ALLOCATED_QTY"].ToString()), reader["DESCR"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString());
                    if (receiptHistoryInfo.ReceivedQty == (receiptHistoryInfo.CompletedQty + receiptHistoryInfo.AllocatedQty))
                    {
                        receiptHistoryInfo.Status = "Completed";
                        if (receiptHistoryInfo.ReceivedQty == receiptHistoryInfo.AllocatedQty)
                        {
                            receiptHistoryInfo.IsPutAwayed = "N";
                        }
                        else
                        {
                            receiptHistoryInfo.IsPutAwayed = "Y";
                        }
                    }
                    else
                    {
                        receiptHistoryInfo.Status = "Pending";
                        receiptHistoryInfo.IsPutAwayed = "N";
                    }
                    receiptHistoryInfos.Add(receiptHistoryInfo);
                }
            }
            return receiptHistoryInfos;
        }

        public List<ReceiptHistoryInfo> GetReceiptHistoryInfoListByID(DataBase dataBase, DbTransaction tran, string receiptID, string whLoginID)
        {
            string where = "WHERE IT.TRANS_TYPE = 'IN' AND SOURCE_TRANS_TYPE = 'ReceiptDetailAdd' AND IT.STATUS = 'OK' AND IT.RECEIPT_ID = @RECEIPT_ID";
            string sql = this.SQL_SELECT_ALL_RECEIPT_HISTORY_LOT.Replace("@CONDITION@", where).Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID) };
            List<ReceiptHistoryInfo> receiptHistoryInfos = new List<ReceiptHistoryInfo>();
            ReceiptHistoryInfo receiptHistoryInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                DateTime CS00002;
                receiptHistoryInfo = new ReceiptHistoryInfo(reader["TRANS_ID"].ToString(), reader["RECEIPT_ID"].ToString(), reader["RECEIPT_LINE_ID"].ToString(), reader["SKU_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["VENDOR_ID"].ToString(), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), reader["TO_LPN_ID"].ToString(), reader["LOT"].ToString(), reader["TO_LOC"].ToString(), decimal.Parse((reader["QTY"].ToString() == "") ? "0" : reader["QTY"].ToString()), reader["HOLD_CODE"].ToString(), decimal.Parse((reader["COMPLETED_QTY"].ToString() == "") ? "0" : reader["COMPLETED_QTY"].ToString()), decimal.Parse((reader["REMAIN_QTY"].ToString() == "") ? "0" : reader["REMAIN_QTY"].ToString()), decimal.Parse((reader["ALLOCATED_QTY"].ToString() == "") ? "0" : reader["ALLOCATED_QTY"].ToString()), reader["DESCR"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00002 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : (CS00002 = new DateTime()), reader["EXTERNAL_LOT"].ToString());
                if (receiptHistoryInfo.ReceivedQty == (receiptHistoryInfo.CompletedQty + receiptHistoryInfo.AllocatedQty))
                {
                    receiptHistoryInfo.Status = "Completed";
                    if (receiptHistoryInfo.ReceivedQty == receiptHistoryInfo.AllocatedQty)
                    {
                        receiptHistoryInfo.IsPutAwayed = "N";
                    }
                    else
                    {
                        receiptHistoryInfo.IsPutAwayed = "Y";
                    }
                }
                else
                {
                    receiptHistoryInfo.Status = "Pending";
                    receiptHistoryInfo.IsPutAwayed = "N";
                }
                receiptHistoryInfos.Add(receiptHistoryInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return receiptHistoryInfos;
        }

        public List<ReceiptHistoryInfo> GetReceiptHistoryInfoListByWhere(string receiptID, string where, string whLoginID)
        {
            string where2 = "WHERE IT.TRANS_TYPE = 'IN' AND SOURCE_TRANS_TYPE = 'ReceiptDetailAdd' AND IT.STATUS = 'OK' AND IT.RECEIPT_ID = @RECEIPT_ID AND " + where;
            string sql = this.SQL_SELECT_ALL_RECEIPT_HISTORY.Replace("@CONDITION@", where2).Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID) };
            List<ReceiptHistoryInfo> receiptHistoryInfos = new List<ReceiptHistoryInfo>();
            ReceiptHistoryInfo receiptHistoryInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                while (reader.Read())
                {
                    receiptHistoryInfo = new ReceiptHistoryInfo(reader["TRANS_ID"].ToString(), reader["RECEIPT_ID"].ToString(), reader["RECEIPT_LINE_ID"].ToString(), reader["SKU_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["VENDOR_ID"].ToString(), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), reader["TO_LPN_ID"].ToString(), reader["LOT"].ToString(), reader["TO_LOC"].ToString(), decimal.Parse((reader["QTY"].ToString() == "") ? "0" : reader["QTY"].ToString()), reader["HOLD_CODE"].ToString(), decimal.Parse((reader["COMPLETED_QTY"].ToString() == "") ? "0" : reader["COMPLETED_QTY"].ToString()), decimal.Parse((reader["REMAIN_QTY"].ToString() == "") ? "0" : reader["REMAIN_QTY"].ToString()), decimal.Parse((reader["ALLOCATED_QTY"].ToString() == "") ? "0" : reader["ALLOCATED_QTY"].ToString()));
                    if (receiptHistoryInfo.ReceivedQty == (receiptHistoryInfo.CompletedQty + receiptHistoryInfo.AllocatedQty))
                    {
                        receiptHistoryInfo.Status = "Completed";
                        if (receiptHistoryInfo.ReceivedQty == receiptHistoryInfo.AllocatedQty)
                        {
                            receiptHistoryInfo.IsPutAwayed = "N";
                        }
                        else
                        {
                            receiptHistoryInfo.IsPutAwayed = "Y";
                        }
                    }
                    else
                    {
                        receiptHistoryInfo.Status = "Pending";
                        receiptHistoryInfo.IsPutAwayed = "N";
                    }
                    receiptHistoryInfos.Add(receiptHistoryInfo);
                }
            }
            return receiptHistoryInfos;
        }

        public List<ReceiptHistoryInfo> GetReceiptHistoryInfoListByWhere(DataBase dataBase, DbTransaction tran, string receiptID, string where, string whLoginID)
        {
            string where2 = "WHERE IT.TRANS_TYPE = 'IN' AND SOURCE_TRANS_TYPE = 'ReceiptDetailAdd' AND IT.STATUS = 'OK' AND IT.RECEIPT_ID = @RECEIPT_ID AND " + where;
            string sql = this.SQL_SELECT_ALL_RECEIPT_HISTORY.Replace("@CONDITION@", where2).Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID) };
            List<ReceiptHistoryInfo> receiptHistoryInfos = new List<ReceiptHistoryInfo>();
            ReceiptHistoryInfo receiptHistoryInfo = null;
            IDataReader reader = dataBase.ExecuteReader(tran, CommandType.Text, sql, paras);
            while (reader.Read())
            {
                receiptHistoryInfo = new ReceiptHistoryInfo(reader["TRANS_ID"].ToString(), reader["RECEIPT_ID"].ToString(), reader["RECEIPT_LINE_ID"].ToString(), reader["SKU_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["VENDOR_ID"].ToString(), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), reader["TO_LPN_ID"].ToString(), reader["LOT"].ToString(), reader["TO_LOC"].ToString(), decimal.Parse((reader["QTY"].ToString() == "") ? "0" : reader["QTY"].ToString()), reader["HOLD_CODE"].ToString(), decimal.Parse((reader["COMPLETED_QTY"].ToString() == "") ? "0" : reader["COMPLETED_QTY"].ToString()), decimal.Parse((reader["REMAIN_QTY"].ToString() == "") ? "0" : reader["REMAIN_QTY"].ToString()), decimal.Parse((reader["ALLOCATED_QTY"].ToString() == "") ? "0" : reader["ALLOCATED_QTY"].ToString()));
                if (receiptHistoryInfo.ReceivedQty == (receiptHistoryInfo.CompletedQty + receiptHistoryInfo.AllocatedQty))
                {
                    receiptHistoryInfo.Status = "Completed";
                    if (receiptHistoryInfo.ReceivedQty == receiptHistoryInfo.AllocatedQty)
                    {
                        receiptHistoryInfo.IsPutAwayed = "N";
                    }
                    else
                    {
                        receiptHistoryInfo.IsPutAwayed = "Y";
                    }
                }
                else
                {
                    receiptHistoryInfo.Status = "Pending";
                    receiptHistoryInfo.IsPutAwayed = "N";
                }
                receiptHistoryInfos.Add(receiptHistoryInfo);
            }
            if (!reader.IsClosed)
            {
                reader.Close();
            }
            return receiptHistoryInfos;
        }

        public DataSet GetReceiptInquiryInfoByQueryEntity(List<string> sqlWhere, ReceiptDetailQueryEntity receiptdetailQuery, string whLoginID, PubParasInfo pubParasInfo)
        {
            string temp = " SELECT R.*,T.[TEXT] AS STATUS_D,T2.[TEXT] AS RECEIPT_TYPE_D FROM (  SELECT DISTINCT ISNULL(SUM(TD.QTY),0) AS PA_PENDING_QTY, ISNULL(SUM(TD2.QTY),0) AS PA_COMPLETED_QTY,  A.ROWID, A.WH_ID, A.RECEIPT_ID, A.RECEIPT_LINE_ID, B.EXTERNAL_RECEIPT_ID,B.EXPECTED_RECEIPT_DATE,A.EXTERN_RECEIPT_ID, A.EXTERN_LINE_ID, A.OWNER_ID, A.PO_ID, A.PO_LINE_ID, A.TARIFF_ID, A.SKU_ID,SKU.DESCR, A.ALT_SKU_ID, A.LPN_ID, A.CASE_ID, A.STATUS, A.RECEIVED_DATE, A.EXPECTED_QTY, A.EXPECTED_QTY - A.RECEIVED_QTY - A.ADJUSTED_QTY - A.REJECTED_QTY AS DIFF_QTY, A.ADJUSTED_QTY, A.RECEIVED_QTY, A.REJECTED_QTY, A.UOM_ID, A.PACK_ID, A.TO_LOC, A.TO_LOT, A.TO_LPN_ID, A.CONDITION_CODE, A.LOT_ATTR01, A.LOT_ATTR02, A.LOT_ATTR03, A.LOT_ATTR04, A.LOT_ATTR05, A.LOT_ATTR06, A.LOT_ATTR07, A.LOT_ATTR08, LAC.DESCR AS LOT_ATTR09, A.LOT_ATTR10, A.PRODUCE_DATE, A.EXPIRY_DATE, A.RECEIPT_DATE, A.EXTERNAL_LOT, A.CASE_CNT, A.INNER_PACK, A.PALLET, A.[CUBE], A.GROSS_WGT, A.NET_WGT, A.OTHER_UNIT1, A.OTHER_UNIT2, A.UNIT_PRICE, A.EXTENDED_PRICE, A.EFFECTIVE_DATE, A.UDF1, A.UDF2, A.UDF3, A.UDF4, A.UDF5, A.UDF6, A.UDF7, A.UDF8, A.UDF9, A.UDF10, A.REASON_CODE, A.PALLET_ID, A.TYPE, A.RETURN_TYPE, A.RETURN_REASON, A.DISPOSITION_TYPE, A.DISPOSITION_CODE, A.RETURN_CONDITION, A.QC_REQUIRED, A.QC_INSPECTED_QTY, A.QC_REJECTED_QTY, A.QC_REJ_REASON, A.QC_STATUS, A.QC_USER, A.QC_AUTO_ADJUST, A.VENDOR_ID, A.VENDOR_NAME, A.MATCH_LOTTABLE, A.CROSS_DOCK_ID, A.REMARK, A.TS, A.CREATED_BY, A.CREATED_DATE, A.UPDATED_BY, A.UPDATED_DATE,B.RECEIPT_TYPE,B.RMA, SKU.SKU_CLASS_ID,LAC.DESCR AS SKU_PROPERTY,A.RECEIVED_QTY * SKU.GROSS_WGT AS TOTAL_GROSS_WGT,A.RECEIVED_QTY * SKU.[CUBE] AS TOTAL_CUBE,A.RECEIVED_QTY * SKU.NET_WGT AS TOTAL_WGT,SC.DESCR AS SKU_CLASS_DESCR FROM @_@RECEIPT_DETAIL A   INNER JOIN @_@RECEIPT B ON A.RECEIPT_ID = B.RECEIPT_ID   LEFT JOIN @_@SKU SKU ON A.OWNER_ID = SKU.OWNER_ID AND A.SKU_ID = SKU.SKU_ID  LEFT JOIN @_@SKU_CLASS SC ON SKU.SKU_CLASS_ID = SC.SKU_CLASS_ID  LEFT JOIN @_@TASK_DETAIL TD ON A.RECEIPT_ID = TD.DOC_ID AND A.RECEIPT_LINE_ID = TD.DOC_LINE_ID AND TD.TASK_TYPE = 'PA' AND TD.STATUS = 'PENDING'  LEFT JOIN @_@TASK_DETAIL TD2 ON A.RECEIPT_ID = TD2.DOC_ID AND A.RECEIPT_LINE_ID = TD2.DOC_LINE_ID AND TD2.TASK_TYPE = 'PA' AND TD2.STATUS = 'COMPLETED' INNER JOIN SYSADM.USER_OWNER UO ON A.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID   LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON A.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性' WHERE B.IS_PALLETIZED='N'  GROUP BY A.ROWID, A.WH_ID, A.RECEIPT_ID, A.RECEIPT_LINE_ID,B.EXTERNAL_RECEIPT_ID,B.EXPECTED_RECEIPT_DATE, A.EXTERN_RECEIPT_ID, A.EXTERN_LINE_ID, A.OWNER_ID, A.PO_ID, A.PO_LINE_ID, A.TARIFF_ID, A.SKU_ID,SKU.DESCR, A.ALT_SKU_ID, A.LPN_ID, A.CASE_ID, A.STATUS, A.RECEIVED_DATE, A.EXPECTED_QTY, A.ADJUSTED_QTY, A.RECEIVED_QTY, A.REJECTED_QTY, A.UOM_ID, A.PACK_ID, A.TO_LOC, A.TO_LOT, A.TO_LPN_ID, A.CONDITION_CODE, A.LOT_ATTR01, A.LOT_ATTR02, A.LOT_ATTR03, A.LOT_ATTR04, A.LOT_ATTR05, A.LOT_ATTR06, A.LOT_ATTR07, A.LOT_ATTR08, LAC.DESCR , A.LOT_ATTR10, A.PRODUCE_DATE, A.EXPIRY_DATE, A.RECEIPT_DATE, A.EXTERNAL_LOT, A.CASE_CNT, A.INNER_PACK, A.PALLET, A.[CUBE], A.GROSS_WGT, A.NET_WGT, A.OTHER_UNIT1, A.OTHER_UNIT2, A.UNIT_PRICE, A.EXTENDED_PRICE, A.EFFECTIVE_DATE, A.UDF1, A.UDF2, A.UDF3, A.UDF4, A.UDF5, A.UDF6, A.UDF7, A.UDF8, A.UDF9, A.UDF10, A.REASON_CODE, A.PALLET_ID, A.TYPE, A.RETURN_TYPE, A.RETURN_REASON, A.DISPOSITION_TYPE, A.DISPOSITION_CODE, A.RETURN_CONDITION, A.QC_REQUIRED, A.QC_INSPECTED_QTY, A.QC_REJECTED_QTY, A.QC_REJ_REASON, A.QC_STATUS, A.QC_USER, A.QC_AUTO_ADJUST, A.VENDOR_ID, A.VENDOR_NAME, A.MATCH_LOTTABLE, A.CROSS_DOCK_ID, A.REMARK, A.TS, A.CREATED_BY, A.CREATED_DATE, A.UPDATED_BY, A.UPDATED_DATE,B.RECEIPT_TYPE,B.RMA, SKU.SKU_CLASS_ID ,SKU.GROSS_WGT,SKU.[CUBE],SKU.NET_WGT,SC.DESCR  UNION  SELECT DISTINCT ISNULL(SUM(TD.QTY),0) AS PA_PENDING_QTY, ISNULL(SUM(TD2.QTY),0) AS PA_COMPLETED_QTY,  A.ROWID, A.WH_ID, A.RECEIPT_ID, A.RECEIPT_LINE_ID, B.EXTERNAL_RECEIPT_ID,B.EXPECTED_RECEIPT_DATE,A.EXTERN_RECEIPT_ID, A.EXTERN_LINE_ID, A.OWNER_ID, A.PO_ID, A.PO_LINE_ID, A.TARIFF_ID, A.SKU_ID,SKU.DESCR, A.ALT_SKU_ID, A.LPN_ID, A.CASE_ID, A.STATUS, A.RECEIVED_DATE, A.EXPECTED_QTY, A.EXPECTED_QTY - A.RECEIVED_QTY - A.ADJUSTED_QTY - A.REJECTED_QTY AS DIFF_QTY, A.ADJUSTED_QTY, A.RECEIVED_QTY, A.REJECTED_QTY, A.UOM_ID, A.PACK_ID, A.TO_LOC, A.TO_LOT, A.TO_LPN_ID, A.CONDITION_CODE, A.LOT_ATTR01, A.LOT_ATTR02, A.LOT_ATTR03, A.LOT_ATTR04, A.LOT_ATTR05, A.LOT_ATTR06, A.LOT_ATTR07, A.LOT_ATTR08, LAC.DESCR AS LOT_ATTR09, A.LOT_ATTR10, A.PRODUCE_DATE, A.EXPIRY_DATE, A.RECEIPT_DATE, A.EXTERNAL_LOT, A.CASE_CNT, A.INNER_PACK, A.PALLET, A.[CUBE], A.GROSS_WGT, A.NET_WGT, A.OTHER_UNIT1, A.OTHER_UNIT2, A.UNIT_PRICE, A.EXTENDED_PRICE, A.EFFECTIVE_DATE, A.UDF1, A.UDF2, A.UDF3, A.UDF4, A.UDF5, A.UDF6, A.UDF7, A.UDF8, A.UDF9, A.UDF10, A.REASON_CODE, A.PALLET_ID, A.TYPE, A.RETURN_TYPE, A.RETURN_REASON, A.DISPOSITION_TYPE, A.DISPOSITION_CODE, A.RETURN_CONDITION, A.QC_REQUIRED, A.QC_INSPECTED_QTY, A.QC_REJECTED_QTY, A.QC_REJ_REASON, A.QC_STATUS, A.QC_USER, A.QC_AUTO_ADJUST, A.VENDOR_ID, A.VENDOR_NAME, A.MATCH_LOTTABLE, A.CROSS_DOCK_ID, A.REMARK, A.TS, A.CREATED_BY, A.CREATED_DATE, A.UPDATED_BY, A.UPDATED_DATE,B.RECEIPT_TYPE,B.RMA, SKU.SKU_CLASS_ID,LAC.DESCR AS SKU_PROPERTY, A.RECEIVED_QTY * SKU.GROSS_WGT AS TOTAL_GROSS_WGT,A.RECEIVED_QTY * SKU.[CUBE] AS TOTAL_CUBE,A.RECEIVED_QTY * SKU.NET_WGT AS TOTAL_WGT,SC.DESCR AS SKU_CLASS_DESCR FROM @_@RECEIPT_DETAIL_LPN A   INNER JOIN @_@RECEIPT B ON A.RECEIPT_ID = B.RECEIPT_ID   LEFT JOIN @_@SKU SKU ON A.OWNER_ID = SKU.OWNER_ID AND A.SKU_ID = SKU.SKU_ID  LEFT JOIN @_@SKU_CLASS SC ON SKU.SKU_CLASS_ID = SC.SKU_CLASS_ID  LEFT JOIN @_@TASK_DETAIL TD ON A.RECEIPT_ID = TD.DOC_ID AND A.RECEIPT_LINE_ID = TD.DOC_LINE_ID AND TD.TASK_TYPE = 'PA' AND TD.STATUS = 'PENDING'  LEFT JOIN @_@TASK_DETAIL TD2 ON A.RECEIPT_ID = TD2.DOC_ID AND A.RECEIPT_LINE_ID = TD2.DOC_LINE_ID AND TD2.TASK_TYPE = 'PA' AND TD2.STATUS = 'COMPLETED' INNER JOIN SYSADM.USER_OWNER UO ON A.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID    LEFT JOIN @_@LOT_ATTRIBUTE_CODE LAC ON A.LOT_ATTR09 = LAC.CODE_ID AND LAC.GROUP_ID = '货品属性' WHERE B.IS_PALLETIZED='Y'  GROUP BY A.ROWID, A.WH_ID, A.RECEIPT_ID, A.RECEIPT_LINE_ID,B.EXTERNAL_RECEIPT_ID,B.EXPECTED_RECEIPT_DATE, A.EXTERN_RECEIPT_ID, A.EXTERN_LINE_ID, A.OWNER_ID, A.PO_ID, A.PO_LINE_ID, A.TARIFF_ID, A.SKU_ID,SKU.DESCR, A.ALT_SKU_ID, A.LPN_ID, A.CASE_ID, A.STATUS, A.RECEIVED_DATE, A.EXPECTED_QTY, A.ADJUSTED_QTY, A.RECEIVED_QTY, A.REJECTED_QTY, A.UOM_ID, A.PACK_ID, A.TO_LOC, A.TO_LOT, A.TO_LPN_ID, A.CONDITION_CODE, A.LOT_ATTR01, A.LOT_ATTR02, A.LOT_ATTR03, A.LOT_ATTR04, A.LOT_ATTR05, A.LOT_ATTR06, A.LOT_ATTR07, A.LOT_ATTR08, LAC.DESCR , A.LOT_ATTR10, A.PRODUCE_DATE, A.EXPIRY_DATE, A.RECEIPT_DATE, A.EXTERNAL_LOT, A.CASE_CNT, A.INNER_PACK, A.PALLET, A.[CUBE], A.GROSS_WGT, A.NET_WGT, A.OTHER_UNIT1, A.OTHER_UNIT2, A.UNIT_PRICE, A.EXTENDED_PRICE, A.EFFECTIVE_DATE, A.UDF1, A.UDF2, A.UDF3, A.UDF4, A.UDF5, A.UDF6, A.UDF7, A.UDF8, A.UDF9, A.UDF10, A.REASON_CODE, A.PALLET_ID, A.TYPE, A.RETURN_TYPE, A.RETURN_REASON, A.DISPOSITION_TYPE, A.DISPOSITION_CODE, A.RETURN_CONDITION, A.QC_REQUIRED, A.QC_INSPECTED_QTY, A.QC_REJECTED_QTY, A.QC_REJ_REASON, A.QC_STATUS, A.QC_USER, A.QC_AUTO_ADJUST, A.VENDOR_ID, A.VENDOR_NAME, A.MATCH_LOTTABLE, A.CROSS_DOCK_ID, A.REMARK, A.TS, A.CREATED_BY, A.CREATED_DATE, A.UPDATED_BY, A.UPDATED_DATE,B.RECEIPT_TYPE,B.RMA, SKU.SKU_CLASS_ID ,SKU.GROSS_WGT,SKU.[CUBE],SKU.NET_WGT,SC.DESCR ) R    LEFT JOIN sysadm.TRANSLATION T ON 'ReceiptStatus.'+R.STATUS = T.ITEM_ID   AND  T.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T.LANG+'-'+T.COUNTRY = @LANGUAGE  LEFT JOIN sysadm.TRANSLATION T2 ON 'ReceiptType.'+R.RECEIPT_TYPE = T2.ITEM_ID  AND  T2.ITEM_TYPE = 'SYS_CODE_DETAIL' AND T2.LANG+'-'+T2.COUNTRY = @LANGUAGE";
            temp = temp.Replace("@_@", whLoginID);
            for (int i = 0; i < sqlWhere.Count; i++)
            {
                if (i == 0)
                {
                    temp = temp + " WHERE " + sqlWhere[i].ToString();
                }
                else
                {
                    temp = temp + " AND " + sqlWhere[i].ToString();
                }
            }
            if (!receiptdetailQuery.IsGetAll)
            {
                temp = PagingHelper.GetPagingSQL(temp, receiptdetailQuery.CurrentPage, receiptdetailQuery.PageSize, receiptdetailQuery.SortField, receiptdetailQuery.SortDirection).Replace("@LANGUAGE", "''" + pubParasInfo.UserLanguage + "''").Replace("@USER_ID", "''" + pubParasInfo.UserID + "''");
            }
            else
            {
                temp = temp.Replace("@LANGUAGE", "'" + pubParasInfo.UserLanguage + "'").Replace("@USER_ID", "'" + pubParasInfo.UserID + "'");
            }
            return DBHelper.ExecuteDataSet(CommandType.Text, temp);
        }

        public DataSet GetReceivedLpnCount(string receiptID, string whLoginID)
        {
            string sql = this.SQL_GET_RECEIVED_LPN_COUNT.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID) };
            return DBHelper.ExecuteDataSet(CommandType.Text, sql, paras);
        }

        public decimal GetReceivedQtyByReceiptDetailID(string receiptID, string lineID, string whLoginID)
        {
            decimal result = 0M;
            string sql = this.SQL_GET_RECEIVED_QTY_BY_RECEIPT_DETAIL_ID.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID), new SqlParameter("@RECEIPT_LINE_ID", lineID) };
            object obj = DBHelper.ExecuteScalar(CommandType.Text, sql, paras);
            if (obj != null)
            {
                result = string.IsNullOrEmpty(obj.ToString()) ? 0M : decimal.Parse(obj.ToString());
            }
            return result;
        }

        public decimal GetReceivedQtyByReceiptDetailID(DataBase dataBase, DbTransaction tran, string receiptID, string lineID, string whLoginID)
        {
            decimal result = 0M;
            string sql = this.SQL_GET_RECEIVED_QTY_BY_RECEIPT_DETAIL_ID.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIPT_ID", receiptID), new SqlParameter("@RECEIPT_LINE_ID", lineID) };
            object obj = dataBase.ExecuteScalar(tran, CommandType.Text, sql, paras);
            if (obj != null)
            {
                result = string.IsNullOrEmpty(obj.ToString()) ? 0M : decimal.Parse(obj.ToString());
            }
            return result;
        }

        public List<RFPutawayDetailInfo> GetRFPutawayDetailInfoByLpn(string lpnID, string userid, string whLoginID)
        {
            string sql = "SELECT TRANS_ID,RECEIPT_ID,RECEIPT_LINE_ID,IT.OWNER_ID,SKU_ID,PACK_ID,UOM_ID,QTY,HOLD_CODE,LOT,LOT_ATTR01 ,LOT_ATTR02,LOT_ATTR03,LOT_ATTR04,LOT_ATTR05,LOT_ATTR06,LOT_ATTR07,LOT_ATTR08,LOT_ATTR09,LOT_ATTR10 ,PRODUCE_DATE,EXPIRY_DATE,RECEIPT_DATE,EXTERNAL_LOT,VENDOR_ID FROM @_@INV_TRANS IT INNER JOIN SYSADM.USER_OWNER UO ON IT.OWNER_ID = UO.OWNER_ID AND UO.[USER_ID] = @USER_ID WHERE IT.TRANS_TYPE = 'IN' AND IT.SOURCE_TRANS_TYPE = 'ReceiptDetailAdd' AND IT.TO_LPN_ID = @TO_LPN_ID AND (SELECT COUNT(TRANS_ID) FROM @_@INV_TRANS  WHERE TRANS_TYPE = 'MV' AND SOURCE_TRANS_TYPE = 'PutawayMove' AND TO_LPN_ID = @TO_LPN_ID) = 0";
            sql = sql.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TO_LPN_ID", lpnID), new SqlParameter("@USER_ID", userid) };
            List<RFPutawayDetailInfo> rfPutawayDetailInfos = new List<RFPutawayDetailInfo>();
            RFPutawayDetailInfo rfPutawayDetailInfo = null;
            using (IDataReader reader = DBHelper.ExecuteReader(CommandType.Text, sql, paras))
            {
                if (reader.Read())
                {
                    DateTime CS00003;
                    rfPutawayDetailInfo = new RFPutawayDetailInfo(reader["TRANS_ID"].ToString(), reader["RECEIPT_ID"].ToString(), reader["RECEIPT_LINE_ID"].ToString(), reader["OWNER_ID"].ToString(), reader["SKU_ID"].ToString(), reader["PACK_ID"].ToString(), reader["UOM_ID"].ToString(), decimal.Parse((reader["QTY"].ToString() == "") ? "0" : reader["QTY"].ToString()), reader["HOLD_CODE"].ToString(), reader["LOT"].ToString(), reader["LOT_ATTR01"].ToString(), reader["LOT_ATTR02"].ToString(), reader["LOT_ATTR03"].ToString(), reader["LOT_ATTR04"].ToString(), reader["LOT_ATTR05"].ToString(), reader["LOT_ATTR06"].ToString(), reader["LOT_ATTR07"].ToString(), reader["LOT_ATTR08"].ToString(), reader["LOT_ATTR09"].ToString(), reader["LOT_ATTR10"].ToString(), (reader["PRODUCE_DATE"].ToString() != "") ? DateTime.Parse(reader["PRODUCE_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["EXPIRY_DATE"].ToString() != "") ? DateTime.Parse(reader["EXPIRY_DATE"].ToString()) : (CS00003 = new DateTime()), (reader["RECEIPT_DATE"].ToString() != "") ? DateTime.Parse(reader["RECEIPT_DATE"].ToString()) : new DateTime(), reader["EXTERNAL_LOT"].ToString(), reader["VENDOR_ID"].ToString());
                    rfPutawayDetailInfos.Add(rfPutawayDetailInfo);
                }
            }
            return rfPutawayDetailInfos;
        }

        public DataSet GetSkuOnLoc(DataBase dataBase, DbTransaction tran, string loc, string whLoginID)
        {
            string sql = " SELECT DISTINCT OWNER_ID,SKU_ID,SUM(QTY + EXPECTED_QTY) AS QTY FROM @_@INV_SKU_LOC WHERE LOC = @LOC GROUP BY OWNER_ID,SKU_ID ";
            sql = sql.Replace("@_@", whLoginID);
            SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@LOC", loc) };
            return dataBase.ExecuteDataSet(tran, CommandType.Text, sql, paras);
        }

        public int InsertReceiptDetail(ReceiptDetailInfo receiptDetailInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                receiptDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_ReceiptDetail_Parameters(receiptDetailInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_RECEIPT_DETAIL.Replace("@_@", whLoginID), paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int InsertReceiptDetail(DataBase dataBase, DbTransaction tran, ReceiptDetailInfo receiptDetailInfo, string whLoginID)
        {
            int result = 0;
            try
            {
                receiptDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_ReceiptDetail_Parameters(receiptDetailInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, this.SQL_INSERT_RECEIPT_DETAIL.Replace("@_@", whLoginID), paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int IsCopyReceiptDetail(DataBase dataBase, DbTransaction tran, string newReceiptID, string oldReceiptID, string userID, string whLoginID)
        {
            int result = 0;
            try
            {
                string strSql = this.SQL_IS_COPY.Replace("@_@", whLoginID).Replace("@RECEIPT_ID", newReceiptID).Replace("@OLD_RECEIPT_ID", oldReceiptID).Replace("@USER_ID", userID);
                result += dataBase.ExecuteNonQuery(tran, CommandType.Text, strSql);
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        private SqlParameter[] Set_ReceiptDetail_Parameters(ReceiptDetailInfo receiptDetailInfo)
        {
            SqlParameter[] paramArray = new SqlParameter[] { 
                new SqlParameter(this.WH_ID, SqlDbType.VarChar, 30), new SqlParameter(this.RECEIPT_ID, SqlDbType.VarChar, 20), new SqlParameter(this.RECEIPT_LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.EXTERN_RECEIPT_ID, SqlDbType.VarChar, 30), new SqlParameter(this.EXTERN_LINE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.OWNER_ID, SqlDbType.VarChar, 20), new SqlParameter(this.PO_ID, SqlDbType.VarChar, 20), new SqlParameter(this.PO_LINE_ID, SqlDbType.Int, 4), new SqlParameter(this.TARIFF_ID, SqlDbType.VarChar, 20), new SqlParameter(this.SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.ALT_SKU_ID, SqlDbType.VarChar, 50), new SqlParameter(this.LPN_ID, SqlDbType.VarChar, 20), new SqlParameter(this.CASE_ID, SqlDbType.VarChar, 20), new SqlParameter(this.STATUS, SqlDbType.VarChar, 20), new SqlParameter(this.RECEIVED_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.EXPECTED_QTY, SqlDbType.Decimal, 13), 
                new SqlParameter(this.ADJUSTED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.RECEIVED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.REJECTED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.UOM_ID, SqlDbType.VarChar, 20), new SqlParameter(this.PACK_ID, SqlDbType.VarChar, 50), new SqlParameter(this.TO_LOC, SqlDbType.VarChar, 20), new SqlParameter(this.TO_LOT, SqlDbType.VarChar, 20), new SqlParameter(this.TO_LPN_ID, SqlDbType.VarChar, 20), new SqlParameter(this.CONDITION_CODE, SqlDbType.VarChar, 20), new SqlParameter(this.LOT_ATTR01, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR02, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR03, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR04, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR05, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR06, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR07, SqlDbType.VarChar, 50), 
                new SqlParameter(this.LOT_ATTR08, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR09, SqlDbType.VarChar, 50), new SqlParameter(this.LOT_ATTR10, SqlDbType.VarChar, 50), new SqlParameter(this.PRODUCE_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.EXPIRY_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.RECEIPT_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.EXTERNAL_LOT, SqlDbType.VarChar, 100), new SqlParameter(this.CASE_CNT, SqlDbType.Decimal, 13), new SqlParameter(this.INNER_PACK, SqlDbType.Decimal, 13), new SqlParameter(this.PALLET, SqlDbType.Decimal, 13), new SqlParameter(this.CUBE, SqlDbType.Float, 8), new SqlParameter(this.GROSS_WGT, SqlDbType.Float, 8), new SqlParameter(this.NET_WGT, SqlDbType.Float, 8), new SqlParameter(this.OTHER_UNIT1, SqlDbType.Float, 8), new SqlParameter(this.OTHER_UNIT2, SqlDbType.Float, 8), new SqlParameter(this.UNIT_PRICE, SqlDbType.Float, 8), 
                new SqlParameter(this.EXTENDED_PRICE, SqlDbType.Float, 8), new SqlParameter(this.EFFECTIVE_DATE, SqlDbType.DateTime, 8), new SqlParameter(this.UDF1, SqlDbType.VarChar, 30), new SqlParameter(this.UDF2, SqlDbType.VarChar, 30), new SqlParameter(this.UDF3, SqlDbType.VarChar, 30), new SqlParameter(this.UDF4, SqlDbType.VarChar, 30), new SqlParameter(this.UDF5, SqlDbType.VarChar, 30), new SqlParameter(this.UDF6, SqlDbType.VarChar, 30), new SqlParameter(this.UDF7, SqlDbType.VarChar, 30), new SqlParameter(this.UDF8, SqlDbType.VarChar, 30), new SqlParameter(this.UDF9, SqlDbType.VarChar, 30), new SqlParameter(this.UDF10, SqlDbType.VarChar, 30), new SqlParameter(this.REASON_CODE, SqlDbType.VarChar, 20), new SqlParameter(this.PALLET_ID, SqlDbType.VarChar, 20), new SqlParameter(this.TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.RETURN_TYPE, SqlDbType.VarChar, 20), 
                new SqlParameter(this.RETURN_REASON, SqlDbType.VarChar, 20), new SqlParameter(this.DISPOSITION_TYPE, SqlDbType.VarChar, 20), new SqlParameter(this.DISPOSITION_CODE, SqlDbType.VarChar, 20), new SqlParameter(this.RETURN_CONDITION, SqlDbType.VarChar, 20), new SqlParameter(this.QC_REQUIRED, SqlDbType.VarChar, 1), new SqlParameter(this.QC_INSPECTED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.QC_REJECTED_QTY, SqlDbType.Decimal, 13), new SqlParameter(this.QC_REJ_REASON, SqlDbType.VarChar, 20), new SqlParameter(this.QC_STATUS, SqlDbType.VarChar, 1), new SqlParameter(this.QC_USER, SqlDbType.VarChar, 20), new SqlParameter(this.QC_AUTO_ADJUST, SqlDbType.VarChar, 1), new SqlParameter(this.RMA, SqlDbType.VarChar, 20), new SqlParameter(this.VENDOR_ID, SqlDbType.VarChar, 20), new SqlParameter(this.VENDOR_NAME, SqlDbType.VarChar, 30), new SqlParameter(this.MATCH_LOTTABLE, SqlDbType.VarChar, 1), new SqlParameter(this.CROSS_DOCK_ID, SqlDbType.VarChar, 20), 
                new SqlParameter(this.REMARK, SqlDbType.VarChar, 0x3e8), new SqlParameter(this.CREATED_BY, SqlDbType.VarChar, 20), new SqlParameter(this.UPDATED_BY, SqlDbType.VarChar, 20)
             };
            if (!string.IsNullOrEmpty(receiptDetailInfo.WhID))
            {
                paramArray[0].Value = receiptDetailInfo.WhID;
            }
            else
            {
                paramArray[0].Value = DBNull.Value;
            }
            paramArray[1].Value = receiptDetailInfo.ReceiptID;
            paramArray[2].Value = receiptDetailInfo.ReceiptLineID;
            paramArray[3].Value = receiptDetailInfo.ExternReceiptID;
            paramArray[4].Value = receiptDetailInfo.ExternLineID;
            paramArray[5].Value = receiptDetailInfo.OwnerID;
            paramArray[6].Value = receiptDetailInfo.PoID;
            paramArray[7].Value = receiptDetailInfo.PoLineID;
            if (!string.IsNullOrEmpty(receiptDetailInfo.TariffID))
            {
                paramArray[8].Value = receiptDetailInfo.TariffID;
            }
            else
            {
                paramArray[8].Value = DBNull.Value;
            }
            paramArray[9].Value = receiptDetailInfo.SkuID;
            paramArray[10].Value = receiptDetailInfo.AltSkuID;
            paramArray[11].Value = receiptDetailInfo.LpnID;
            if (!string.IsNullOrEmpty(receiptDetailInfo.CaseID))
            {
                paramArray[12].Value = receiptDetailInfo.CaseID;
            }
            else
            {
                paramArray[12].Value = DBNull.Value;
            }
            paramArray[13].Value = receiptDetailInfo.Status;
            DateTime CS00003 = new DateTime();
            if (receiptDetailInfo.ReceivedDate != CS00003)
            {
                paramArray[14].Value = receiptDetailInfo.ReceivedDate;
            }
            else
            {
                paramArray[14].Value = DBNull.Value;
            }
            paramArray[15].Value = receiptDetailInfo.ExpectedQty;
            paramArray[0x10].Value = receiptDetailInfo.AdjustedQty;
            paramArray[0x11].Value = receiptDetailInfo.ReceivedQty;
            paramArray[0x12].Value = receiptDetailInfo.RejectedQty;
            paramArray[0x13].Value = receiptDetailInfo.UomID;
            paramArray[20].Value = receiptDetailInfo.PackID;
            paramArray[0x15].Value = receiptDetailInfo.ToLoc;
            if (!string.IsNullOrEmpty(receiptDetailInfo.ToLot))
            {
                paramArray[0x16].Value = receiptDetailInfo.ToLot;
            }
            else
            {
                paramArray[0x16].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailInfo.ToLpnID))
            {
                paramArray[0x17].Value = receiptDetailInfo.ToLpnID;
            }
            else
            {
                paramArray[0x17].Value = DBNull.Value;
            }
            paramArray[0x18].Value = receiptDetailInfo.ConditionCode;
            paramArray[0x19].Value = receiptDetailInfo.ReceiptLot.LotAttr01;
            paramArray[0x1a].Value = receiptDetailInfo.ReceiptLot.LotAttr02;
            paramArray[0x1b].Value = receiptDetailInfo.ReceiptLot.LotAttr03;
            if (!string.IsNullOrEmpty(receiptDetailInfo.ReceiptLot.LotAttr04))
            {
                paramArray[0x1c].Value = receiptDetailInfo.ReceiptLot.LotAttr04;
            }
            else
            {
                paramArray[0x1c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailInfo.ReceiptLot.LotAttr05))
            {
                paramArray[0x1d].Value = receiptDetailInfo.ReceiptLot.LotAttr05;
            }
            else
            {
                paramArray[0x1d].Value = DBNull.Value;
            }
            paramArray[30].Value = receiptDetailInfo.ReceiptLot.LotAttr06;
            paramArray[0x1f].Value = receiptDetailInfo.ReceiptLot.LotAttr07;
            paramArray[0x20].Value = receiptDetailInfo.ReceiptLot.LotAttr08;
            paramArray[0x21].Value = receiptDetailInfo.ReceiptLot.LotAttr09;
            paramArray[0x22].Value = receiptDetailInfo.ReceiptLot.LotAttr10;
            CS00003 = new DateTime();
            if (receiptDetailInfo.ReceiptLot.ProduceDate != CS00003)
            {
                paramArray[0x23].Value = receiptDetailInfo.ReceiptLot.ProduceDate;
            }
            else
            {
                paramArray[0x23].Value = DBNull.Value;
            }
            CS00003 = new DateTime();
            if (receiptDetailInfo.ReceiptLot.ExpiryDate != CS00003)
            {
                paramArray[0x24].Value = receiptDetailInfo.ReceiptLot.ExpiryDate;
            }
            else
            {
                paramArray[0x24].Value = DBNull.Value;
            }
            if (receiptDetailInfo.ReceiptLot.ReceiptDate != new DateTime())
            {
                paramArray[0x25].Value = receiptDetailInfo.ReceiptLot.ReceiptDate;
            }
            else
            {
                paramArray[0x25].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailInfo.ReceiptLot.ExternalLot))
            {
                paramArray[0x26].Value = receiptDetailInfo.ReceiptLot.ExternalLot;
            }
            else
            {
                paramArray[0x26].Value = DBNull.Value;
            }
            paramArray[0x27].Value = receiptDetailInfo.CaseCnt;
            paramArray[40].Value = receiptDetailInfo.InnerPack;
            paramArray[0x29].Value = receiptDetailInfo.Pallet;
            paramArray[0x2a].Value = receiptDetailInfo.Cube;
            paramArray[0x2b].Value = receiptDetailInfo.GrossWgt;
            paramArray[0x2c].Value = receiptDetailInfo.NetWgt;
            paramArray[0x2d].Value = receiptDetailInfo.OtherUnit1;
            paramArray[0x2e].Value = receiptDetailInfo.OtherUnit2;
            paramArray[0x2f].Value = receiptDetailInfo.UnitPrice;
            paramArray[0x30].Value = receiptDetailInfo.ExtendedPrice;
            paramArray[0x31].Value = receiptDetailInfo.EffectiveDate;
            if (!string.IsNullOrEmpty(receiptDetailInfo.Udf1))
            {
                paramArray[50].Value = receiptDetailInfo.Udf1;
            }
            else
            {
                paramArray[50].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailInfo.Udf2))
            {
                paramArray[0x33].Value = receiptDetailInfo.Udf2;
            }
            else
            {
                paramArray[0x33].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailInfo.Udf3))
            {
                paramArray[0x34].Value = receiptDetailInfo.Udf3;
            }
            else
            {
                paramArray[0x34].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailInfo.Udf4))
            {
                paramArray[0x35].Value = receiptDetailInfo.Udf4;
            }
            else
            {
                paramArray[0x35].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailInfo.Udf5))
            {
                paramArray[0x36].Value = receiptDetailInfo.Udf5;
            }
            else
            {
                paramArray[0x36].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailInfo.Udf6))
            {
                paramArray[0x37].Value = receiptDetailInfo.Udf6;
            }
            else
            {
                paramArray[0x37].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailInfo.Udf7))
            {
                paramArray[0x38].Value = receiptDetailInfo.Udf7;
            }
            else
            {
                paramArray[0x38].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailInfo.Udf8))
            {
                paramArray[0x39].Value = receiptDetailInfo.Udf8;
            }
            else
            {
                paramArray[0x39].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailInfo.Udf9))
            {
                paramArray[0x3a].Value = receiptDetailInfo.Udf9;
            }
            else
            {
                paramArray[0x3a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailInfo.Udf10))
            {
                paramArray[0x3b].Value = receiptDetailInfo.Udf10;
            }
            else
            {
                paramArray[0x3b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailInfo.ReasonCode))
            {
                paramArray[60].Value = receiptDetailInfo.ReasonCode;
            }
            else
            {
                paramArray[60].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailInfo.PalletID))
            {
                paramArray[0x3d].Value = receiptDetailInfo.PalletID;
            }
            else
            {
                paramArray[0x3d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailInfo.Type))
            {
                paramArray[0x3e].Value = receiptDetailInfo.Type;
            }
            else
            {
                paramArray[0x3e].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailInfo.ReturnType))
            {
                paramArray[0x3f].Value = receiptDetailInfo.ReturnType;
            }
            else
            {
                paramArray[0x3f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailInfo.ReturnReason))
            {
                paramArray[0x40].Value = receiptDetailInfo.ReturnReason;
            }
            else
            {
                paramArray[0x40].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailInfo.DispositionType))
            {
                paramArray[0x41].Value = receiptDetailInfo.DispositionType;
            }
            else
            {
                paramArray[0x41].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailInfo.DispositionCode))
            {
                paramArray[0x42].Value = receiptDetailInfo.DispositionCode;
            }
            else
            {
                paramArray[0x42].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailInfo.ReturnCondition))
            {
                paramArray[0x43].Value = receiptDetailInfo.ReturnCondition;
            }
            else
            {
                paramArray[0x43].Value = DBNull.Value;
            }
            paramArray[0x44].Value = receiptDetailInfo.QcRequired;
            paramArray[0x45].Value = receiptDetailInfo.QcInspectedQty;
            paramArray[70].Value = receiptDetailInfo.QcRejectedQty;
            if (!string.IsNullOrEmpty(receiptDetailInfo.QcRejReason))
            {
                paramArray[0x47].Value = receiptDetailInfo.QcRejReason;
            }
            else
            {
                paramArray[0x47].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailInfo.QcStatus))
            {
                paramArray[0x48].Value = receiptDetailInfo.QcStatus;
            }
            else
            {
                paramArray[0x48].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailInfo.QcUser))
            {
                paramArray[0x49].Value = receiptDetailInfo.QcUser;
            }
            else
            {
                paramArray[0x49].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailInfo.QcAutoAdjust))
            {
                paramArray[0x4a].Value = receiptDetailInfo.QcAutoAdjust;
            }
            else
            {
                paramArray[0x4a].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailInfo.Rma))
            {
                paramArray[0x4b].Value = receiptDetailInfo.Rma;
            }
            else
            {
                paramArray[0x4b].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailInfo.VendorID))
            {
                paramArray[0x4c].Value = receiptDetailInfo.VendorID;
            }
            else
            {
                paramArray[0x4c].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailInfo.VendorName))
            {
                paramArray[0x4d].Value = receiptDetailInfo.VendorName;
            }
            else
            {
                paramArray[0x4d].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailInfo.MatchLottable))
            {
                paramArray[0x4e].Value = receiptDetailInfo.MatchLottable;
            }
            else
            {
                paramArray[0x4e].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailInfo.CrossDockID))
            {
                paramArray[0x4f].Value = receiptDetailInfo.CrossDockID;
            }
            else
            {
                paramArray[0x4f].Value = DBNull.Value;
            }
            if (!string.IsNullOrEmpty(receiptDetailInfo.Remark))
            {
                paramArray[80].Value = receiptDetailInfo.Remark;
            }
            else
            {
                paramArray[80].Value = DBNull.Value;
            }
            paramArray[0x51].Value = receiptDetailInfo.CreatedBy;
            paramArray[0x52].Value = receiptDetailInfo.UpdatedBy;
            return paramArray;
        }

        public int UpdatePutawayTaskDetailStatus(DataBase dataBase, DbTransaction tran, string taskDetailID, string taskStatus, string finalLoc, string toTransID, string updatedBy, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = this.SQL_UPDATE_TASK_DETAIL_STATUS.Replace("@_@", whLoginID);
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@TASK_DETAIL_ID", taskDetailID), new SqlParameter("@STATUS", taskStatus), new SqlParameter("@FINAL_TO_LOC", finalLoc), new SqlParameter("@TO_TRANS_ID", toTransID), new SqlParameter("@UPDATED_BY", updatedBy) };
                result = dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateReceiptDetail(ReceiptDetailInfo receiptDetailInfo, string whLoginID)
        {
            int result = 0;
            string sql = string.Empty;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                if (this.CheckReceiptIsPalletized(db, tran, receiptDetailInfo.ReceiptID, whLoginID))
                {
                    sql = this.SQL_UPDATE_RECEIPT_DETAIL_LPN.Replace("@_@", whLoginID);
                }
                else
                {
                    sql = this.SQL_UPDATE_RECEIPT_DETAIL.Replace("@_@", whLoginID);
                }
                receiptDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_ReceiptDetail_Parameters(receiptDetailInfo);
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateReceiptDetail(DataBase dataBase, DbTransaction tran, ReceiptDetailInfo receiptDetailInfo, string whLoginID)
        {
            int result = 0;
            string sql = string.Empty;
            try
            {
                if (this.CheckReceiptIsPalletized(dataBase, tran, receiptDetailInfo.ReceiptID, whLoginID))
                {
                    sql = this.SQL_UPDATE_RECEIPT_DETAIL_LPN.Replace("@_@", whLoginID);
                }
                else
                {
                    sql = this.SQL_UPDATE_RECEIPT_DETAIL.Replace("@_@", whLoginID);
                }
                receiptDetailInfo.WhID = whLoginID.Substring(0, whLoginID.Length - 1);
                SqlParameter[] paras = this.Set_ReceiptDetail_Parameters(receiptDetailInfo);
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateReceiptDetailStatus(string receiptID, string status, string updatedBy, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = this.SQL_UPDATE_RECEIPT_DETAIL_STAUTS.Replace("@_@", whLoginID);
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@RECEIPT_ID", receiptID) };
                result = DBHelper.ExecuteNonQuery(CommandType.Text, sql, paras);
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateReceiptDetailStatus(DataBase dataBase, DbTransaction tran, string receiptID, string status, string updatedBy, string whLoginID)
        {
            int result = 0;
            try
            {
                string sql = this.SQL_UPDATE_RECEIPT_DETAIL_STAUTS.Replace("@_@", whLoginID);
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@STATUS", status), new SqlParameter("@UPDATED_BY", updatedBy), new SqlParameter("@RECEIPT_ID", receiptID) };
                result = dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }

        public int UpdateReceiptDetailStatus4Receiving(ReceiptDetailInfo receiptdetailInfo, string whLoginID)
        {
            int result = 0;
            DataBase db = new DataBase();
            DbConnection conn = db.connection;
            conn.Open();
            DbTransaction tran = conn.BeginTransaction();
            try
            {
                string sql = this.SQL_UPDATE_RECEIPT_DETAIL_FOR_RECEIGING.Replace("@_@", whLoginID);
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIVED_QTY", receiptdetailInfo.ReceivedQty), new SqlParameter("@RECEIVED_DATE", receiptdetailInfo.ReceivedDate), new SqlParameter("@RECEIPT_DATE", receiptdetailInfo.ReceiptLot.ReceiptDate), new SqlParameter("@STATUS", receiptdetailInfo.Status), new SqlParameter("@LPN_ID", receiptdetailInfo.LpnID), new SqlParameter("@TO_LOC", receiptdetailInfo.ToLoc), new SqlParameter("@TO_LOT", receiptdetailInfo.ToLot), new SqlParameter("@TO_LPN_ID", receiptdetailInfo.ToLpnID), new SqlParameter("@UPDATED_BY", receiptdetailInfo.UpdatedBy), new SqlParameter("@RECEIPT_ID", receiptdetailInfo.ReceiptID), new SqlParameter("@RECEIPT_LINE_ID", receiptdetailInfo.ReceiptLineID) };
                if (paras != null)
                {
                    result += db.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
                }
                tran.Commit();
            }
            catch (SqlException sqlEx)
            {
                tran.Rollback();
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int UpdateReceiptDetailStatus4Receiving(DataBase dataBase, DbTransaction tran, ReceiptDetailInfo receiptdetailInfo, string whLoginID)
        {
            int result = 0;
            string sql = this.SQL_UPDATE_RECEIPT_DETAIL_FOR_RECEIGING.Replace("@_@", whLoginID);
            try
            {
                SqlParameter[] paras = new SqlParameter[] { new SqlParameter("@RECEIVED_QTY", receiptdetailInfo.ReceivedQty), new SqlParameter("@RECEIVED_DATE", receiptdetailInfo.ReceivedDate), new SqlParameter("@RECEIPT_DATE", receiptdetailInfo.ReceiptLot.ReceiptDate), new SqlParameter("@STATUS", receiptdetailInfo.Status), new SqlParameter("@LPN_ID", receiptdetailInfo.LpnID), new SqlParameter("@TO_LOC", receiptdetailInfo.ToLoc), new SqlParameter("@TO_LOT", receiptdetailInfo.ToLot), new SqlParameter("@TO_LPN_ID", receiptdetailInfo.ToLpnID), new SqlParameter("@UPDATED_BY", receiptdetailInfo.UpdatedBy), new SqlParameter("@RECEIPT_ID", receiptdetailInfo.ReceiptID), new SqlParameter("@RECEIPT_LINE_ID", receiptdetailInfo.ReceiptLineID) };
                if (paras != null)
                {
                    result += dataBase.ExecuteNonQuery(tran, CommandType.Text, sql, paras);
                }
            }
            catch (SqlException sqlEx)
            {
                CommonErrorHandler.ErrorHandler(sqlEx, LogType.Error);
            }
            return result;
        }
    }
}

